﻿ALTER TRIGGER Governorates_Tracking_Insert
ON [dbo].[Governorates] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Governorates_Tracking ([GovernorateID] ,IsDeleted,LastUpdatedDate)
	SELECT [GovernorateID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Governorates_Tracking_Update
ON [dbo].[Governorates] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Governorates_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Governorates_Tracking s JOIN INSERTED i
    ON s.GovernorateID = i.GovernorateID
END

GO

ALTER TRIGGER Governorates_Tracking_Delete
ON [dbo].[Governorates] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Governorates_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Governorates_Tracking s JOIN DELETED i
    ON s.GovernorateID = i.GovernorateID
END

GO

ALTER PROCEDURE Sync.SP_Governorates_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[GovernorateID],
		g.[GovernorateName],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Governorates] g RIGHT JOIN Sync.Governorates_Tracking t  
    ON t.GovernorateID = g.GovernorateID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Governorates_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Governorates_Temp TABLE 
		(
			GovernorateID UniqueIdentifier,
			[GovernorateName] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Governorates_Temp 
	   SELECT	Tbl.Col.value('GovernorateID[1]','uniqueidentifier') GovernorateID,
                Tbl.Col.value('GovernorateName[1]', 'nvarchar(50)') [GovernorateName],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Governorates') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Governorates] ([GovernorateID],[GovernorateName])
	SELECT t.[GovernorateID],t.[GovernorateName]
	FROM  @Governorates_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Governorates_Tracking s
        WHERE t.GovernorateID = s.GovernorateID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Governorates]
	SET [Governorates].[GovernorateName] = t.[GovernorateName] 
	FROM [dbo].[Governorates] s JOIN @Governorates_Temp t 
    ON t.GovernorateID = s.GovernorateID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Governorates_Tracking r
				  WHERE t.GovernorateID = r.GovernorateID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Governorates]
    FROM @Governorates_Temp t JOIN [dbo].[Governorates] s
    ON t.GovernorateID = s.GovernorateID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Centers_Tracking_Insert
ON [dbo].[Centers] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Centers_Tracking ([CenterID] ,IsDeleted,LastUpdatedDate)
	SELECT [CenterID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Centers_Tracking_Update
ON [dbo].[Centers] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Centers_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Centers_Tracking s JOIN INSERTED i
    ON s.CenterID = i.CenterID
END

GO

ALTER TRIGGER Centers_Tracking_Delete
ON [dbo].[Centers] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Centers_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Centers_Tracking s JOIN DELETED i
    ON s.CenterID = i.CenterID
END

GO

ALTER PROCEDURE Sync.SP_Centers_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CenterID],
		g.[CenterName],g.[GovernorateID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Centers] g RIGHT JOIN Sync.Centers_Tracking t  
    ON t.CenterID = g.CenterID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Centers_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Centers_Temp TABLE 
		(
			CenterID UniqueIdentifier,
			[CenterName] nvarchar(50),
				[GovernorateID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Centers_Temp 
	   SELECT	Tbl.Col.value('CenterID[1]','uniqueidentifier') CenterID,
                Tbl.Col.value('CenterName[1]', 'nvarchar(50)') [CenterName],
Tbl.Col.value('GovernorateID[1]', 'uniqueidentifier') [GovernorateID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Centers') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Centers] ([CenterID],[CenterName],[GovernorateID])
	SELECT t.[CenterID],t.[CenterName],t.[GovernorateID]
	FROM  @Centers_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Centers_Tracking s
        WHERE t.CenterID = s.CenterID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Centers]
	SET [Centers].[CenterName] = t.[CenterName],[Centers].[GovernorateID] = t.[GovernorateID] 
	FROM [dbo].[Centers] s JOIN @Centers_Temp t 
    ON t.CenterID = s.CenterID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Centers_Tracking r
				  WHERE t.CenterID = r.CenterID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Centers]
    FROM @Centers_Temp t JOIN [dbo].[Centers] s
    ON t.CenterID = s.CenterID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Projects_Tracking_Insert
ON [dbo].[Projects] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Projects_Tracking ([ProjectID] ,IsDeleted,LastUpdatedDate)
	SELECT [ProjectID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Projects_Tracking_Update
ON [dbo].[Projects] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Projects_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Projects_Tracking s JOIN INSERTED i
    ON s.ProjectID = i.ProjectID
END

GO

ALTER TRIGGER Projects_Tracking_Delete
ON [dbo].[Projects] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Projects_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Projects_Tracking s JOIN DELETED i
    ON s.ProjectID = i.ProjectID
END

GO

ALTER PROCEDURE Sync.SP_Projects_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ProjectID],
		g.[ProjectName],g.[ProfitPercent],g.[ParentID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Projects] g RIGHT JOIN Sync.Projects_Tracking t  
    ON t.ProjectID = g.ProjectID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Projects_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Projects_Temp TABLE 
		(
			ProjectID UniqueIdentifier,
			[ProjectName] nvarchar(50),
				[ProfitPercent] decimal,
				[ParentID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Projects_Temp 
	   SELECT	Tbl.Col.value('ProjectID[1]','uniqueidentifier') ProjectID,
                Tbl.Col.value('ProjectName[1]', 'nvarchar(50)') [ProjectName],
Tbl.Col.value('ProfitPercent[1]', 'decimal') [ProfitPercent],
Tbl.Col.value('ParentID[1]', 'uniqueidentifier') [ParentID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Projects') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ProfitPercent],[ParentID])
	SELECT t.[ProjectID],t.[ProjectName],t.[ProfitPercent],t.[ParentID]
	FROM  @Projects_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Projects_Tracking s
        WHERE t.ProjectID = s.ProjectID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Projects]
	SET [Projects].[ProjectName] = t.[ProjectName],[Projects].[ProfitPercent] = t.[ProfitPercent],[Projects].[ParentID] = t.[ParentID] 
	FROM [dbo].[Projects] s JOIN @Projects_Temp t 
    ON t.ProjectID = s.ProjectID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Projects_Tracking r
				  WHERE t.ProjectID = r.ProjectID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Projects]
    FROM @Projects_Temp t JOIN [dbo].[Projects] s
    ON t.ProjectID = s.ProjectID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER ProjectStages_Tracking_Insert
ON [dbo].[ProjectStages] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.ProjectStages_Tracking ([ProjectStageID] ,IsDeleted,LastUpdatedDate)
	SELECT [ProjectStageID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER ProjectStages_Tracking_Update
ON [dbo].[ProjectStages] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.ProjectStages_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.ProjectStages_Tracking s JOIN INSERTED i
    ON s.ProjectStageID = i.ProjectStageID
END

GO

ALTER TRIGGER ProjectStages_Tracking_Delete
ON [dbo].[ProjectStages] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.ProjectStages_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.ProjectStages_Tracking s JOIN DELETED i
    ON s.ProjectStageID = i.ProjectStageID
END

GO

ALTER PROCEDURE Sync.SP_ProjectStages_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ProjectStageID],
		g.[ProjectID],g.[Stage],g.[AgeLimit],g.[From],g.[To],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[ProjectStages] g RIGHT JOIN Sync.ProjectStages_Tracking t  
    ON t.ProjectStageID = g.ProjectStageID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_ProjectStages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @ProjectStages_Temp TABLE 
		(
			ProjectStageID UniqueIdentifier,
			[ProjectID] uniqueidentifier,
				[Stage] int,
				[AgeLimit] int,
				[From] float,
				[To] float,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @ProjectStages_Temp 
	   SELECT	Tbl.Col.value('ProjectStageID[1]','uniqueidentifier') ProjectStageID,
                Tbl.Col.value('ProjectID[1]', 'uniqueidentifier') [ProjectID],
Tbl.Col.value('Stage[1]', 'int') [Stage],
Tbl.Col.value('AgeLimit[1]', 'int') [AgeLimit],
Tbl.Col.value('From[1]', 'float') [From],
Tbl.Col.value('To[1]', 'float') [To],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/ProjectStages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[ProjectStages] ([ProjectStageID],[ProjectID],[Stage],[AgeLimit],[From],[To])
	SELECT t.[ProjectStageID],t.[ProjectID],t.[Stage],t.[AgeLimit],t.[From],t.[To]
	FROM  @ProjectStages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.ProjectStages_Tracking s
        WHERE t.ProjectStageID = s.ProjectStageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[ProjectStages]
	SET [ProjectStages].[ProjectID] = t.[ProjectID],[ProjectStages].[Stage] = t.[Stage],[ProjectStages].[AgeLimit] = t.[AgeLimit],[ProjectStages].[From] = t.[From],[ProjectStages].[To] = t.[To] 
	FROM [dbo].[ProjectStages] s JOIN @ProjectStages_Temp t 
    ON t.ProjectStageID = s.ProjectStageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.ProjectStages_Tracking r
				  WHERE t.ProjectStageID = r.ProjectStageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[ProjectStages]
    FROM @ProjectStages_Temp t JOIN [dbo].[ProjectStages] s
    ON t.ProjectStageID = s.ProjectStageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER IssuePlace_Tracking_Insert
ON [dbo].[IssuePlace] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.IssuePlace_Tracking ([IssuePlaceID] ,IsDeleted,LastUpdatedDate)
	SELECT [IssuePlaceID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER IssuePlace_Tracking_Update
ON [dbo].[IssuePlace] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.IssuePlace_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.IssuePlace_Tracking s JOIN INSERTED i
    ON s.IssuePlaceID = i.IssuePlaceID
END

GO

ALTER TRIGGER IssuePlace_Tracking_Delete
ON [dbo].[IssuePlace] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.IssuePlace_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.IssuePlace_Tracking s JOIN DELETED i
    ON s.IssuePlaceID = i.IssuePlaceID
END

GO

ALTER PROCEDURE Sync.SP_IssuePlace_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[IssuePlaceID],
		g.[IssuePlaceName],g.[CenterID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[IssuePlace] g RIGHT JOIN Sync.IssuePlace_Tracking t  
    ON t.IssuePlaceID = g.IssuePlaceID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_IssuePlace_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @IssuePlace_Temp TABLE 
		(
			IssuePlaceID UniqueIdentifier,
			[IssuePlaceName] nvarchar(50),
				[CenterID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @IssuePlace_Temp 
	   SELECT	Tbl.Col.value('IssuePlaceID[1]','uniqueidentifier') IssuePlaceID,
                Tbl.Col.value('IssuePlaceName[1]', 'nvarchar(50)') [IssuePlaceName],
Tbl.Col.value('CenterID[1]', 'uniqueidentifier') [CenterID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/IssuePlace') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[IssuePlace] ([IssuePlaceID],[IssuePlaceName],[CenterID])
	SELECT t.[IssuePlaceID],t.[IssuePlaceName],t.[CenterID]
	FROM  @IssuePlace_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.IssuePlace_Tracking s
        WHERE t.IssuePlaceID = s.IssuePlaceID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[IssuePlace]
	SET [IssuePlace].[IssuePlaceName] = t.[IssuePlaceName],[IssuePlace].[CenterID] = t.[CenterID] 
	FROM [dbo].[IssuePlace] s JOIN @IssuePlace_Temp t 
    ON t.IssuePlaceID = s.IssuePlaceID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.IssuePlace_Tracking r
				  WHERE t.IssuePlaceID = r.IssuePlaceID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[IssuePlace]
    FROM @IssuePlace_Temp t JOIN [dbo].[IssuePlace] s
    ON t.IssuePlaceID = s.IssuePlaceID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Villages_Tracking_Insert
ON [dbo].[Villages] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Villages_Tracking ([VillageID] ,IsDeleted,LastUpdatedDate)
	SELECT [VillageID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Villages_Tracking_Update
ON [dbo].[Villages] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Villages_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Villages_Tracking s JOIN INSERTED i
    ON s.VillageID = i.VillageID
END

GO

ALTER TRIGGER Villages_Tracking_Delete
ON [dbo].[Villages] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Villages_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Villages_Tracking s JOIN DELETED i
    ON s.VillageID = i.VillageID
END

GO

ALTER PROCEDURE Sync.SP_Villages_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[VillageID],
		g.[VillageName],g.[CenterID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Villages] g RIGHT JOIN Sync.Villages_Tracking t  
    ON t.VillageID = g.VillageID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Villages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Villages_Temp TABLE 
		(
			VillageID UniqueIdentifier,
			[VillageName] nvarchar(50),
				[CenterID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Villages_Temp 
	   SELECT	Tbl.Col.value('VillageID[1]','uniqueidentifier') VillageID,
                Tbl.Col.value('VillageName[1]', 'nvarchar(50)') [VillageName],
Tbl.Col.value('CenterID[1]', 'uniqueidentifier') [CenterID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Villages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Villages] ([VillageID],[VillageName],[CenterID])
	SELECT t.[VillageID],t.[VillageName],t.[CenterID]
	FROM  @Villages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Villages_Tracking s
        WHERE t.VillageID = s.VillageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Villages]
	SET [Villages].[VillageName] = t.[VillageName],[Villages].[CenterID] = t.[CenterID] 
	FROM [dbo].[Villages] s JOIN @Villages_Temp t 
    ON t.VillageID = s.VillageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Villages_Tracking r
				  WHERE t.VillageID = r.VillageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Villages]
    FROM @Villages_Temp t JOIN [dbo].[Villages] s
    ON t.VillageID = s.VillageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER RolesPermissions_Tracking_Insert
ON [dbo].[RolesPermissions] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.RolesPermissions_Tracking ([PermissionID],[RoleID] ,IsDeleted,LastUpdatedDate)
	SELECT [PermissionID],[RoleID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER RolesPermissions_Tracking_Update
ON [dbo].[RolesPermissions] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.RolesPermissions_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.RolesPermissions_Tracking s JOIN INSERTED i
    ON s.PermissionID = i.PermissionID AND s.RoleID = i.RoleID
END

GO

ALTER TRIGGER RolesPermissions_Tracking_Delete
ON [dbo].[RolesPermissions] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.RolesPermissions_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.RolesPermissions_Tracking s JOIN DELETED i
    ON s.PermissionID = i.PermissionID AND s.RoleID = i.RoleID
END

GO

ALTER PROCEDURE Sync.SP_RolesPermissions_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[PermissionID],t.[RoleID],
		
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[RolesPermissions] g RIGHT JOIN Sync.RolesPermissions_Tracking t  
    ON t.PermissionID = g.PermissionID AND t.RoleID = g.RoleID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_RolesPermissions_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @RolesPermissions_Temp TABLE 
		(
			PermissionID UniqueIdentifier,
	RoleID UniqueIdentifier,
			
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @RolesPermissions_Temp 
	   SELECT	Tbl.Col.value('PermissionID[1]','uniqueidentifier') PermissionID,
Tbl.Col.value('RoleID[1]','uniqueidentifier') RoleID,
                
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/RolesPermissions') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[RolesPermissions] ([PermissionID],[RoleID])
	SELECT t.[PermissionID],t.[RoleID]
	FROM  @RolesPermissions_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.RolesPermissions_Tracking s
        WHERE t.PermissionID = s.PermissionID AND t.RoleID = s.RoleID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[RolesPermissions]
    FROM @RolesPermissions_Temp t JOIN [dbo].[RolesPermissions] s
    ON t.PermissionID = s.PermissionID AND t.RoleID = s.RoleID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Committees_Tracking_Insert
ON [dbo].[Committees] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Committees_Tracking ([CommitteeID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Committees_Tracking_Update
ON [dbo].[Committees] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Committees_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Committees_Tracking s JOIN INSERTED i
    ON s.CommitteeID = i.CommitteeID
END

GO

ALTER TRIGGER Committees_Tracking_Delete
ON [dbo].[Committees] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Committees_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Committees_Tracking s JOIN DELETED i
    ON s.CommitteeID = i.CommitteeID
END

GO

ALTER PROCEDURE Sync.SP_Committees_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeID],
		g.[CommitteeName],g.[CenterID],g.[IsMainCommittee],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Committees] g RIGHT JOIN Sync.Committees_Tracking t  
    ON t.CommitteeID = g.CommitteeID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_Committees_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Committees_Temp TABLE 
		(
			CommitteeID UniqueIdentifier,
			[CommitteeName] nvarchar(50),
				[CenterID] uniqueidentifier,
				[IsMainCommittee] bit,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Committees_Temp 
	   SELECT	Tbl.Col.value('CommitteeID[1]','uniqueidentifier') CommitteeID,
                Tbl.Col.value('CommitteeName[1]', 'nvarchar(50)') [CommitteeName],
Tbl.Col.value('CenterID[1]', 'uniqueidentifier') [CenterID],
Tbl.Col.value('IsMainCommittee[1]', 'bit') [IsMainCommittee],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Committees') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Committees] ([CommitteeID],[CommitteeName],[CenterID],[IsMainCommittee])
	SELECT t.[CommitteeID],t.[CommitteeName],t.[CenterID],t.[IsMainCommittee]
	FROM  @Committees_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Committees_Tracking s
        WHERE t.CommitteeID = s.CommitteeID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Committees]
	SET [Committees].[CommitteeName] = t.[CommitteeName],[Committees].[CenterID] = t.[CenterID],[Committees].[IsMainCommittee] = t.[IsMainCommittee] 
	FROM [dbo].[Committees] s JOIN @Committees_Temp t 
    ON t.CommitteeID = s.CommitteeID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Committees_Tracking r
				  WHERE t.CommitteeID = r.CommitteeID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Committees]
    FROM @Committees_Temp t JOIN [dbo].[Committees] s
    ON t.CommitteeID = s.CommitteeID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Customers_Tracking_Insert
ON [dbo].[Customers] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Customers_Tracking ([CustomerID] ,IsDeleted,LastUpdatedDate)
	SELECT [CustomerID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Customers_Tracking_Update
ON [dbo].[Customers] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Customers_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Customers_Tracking s JOIN INSERTED i
    ON s.CustomerID = i.CustomerID
END

GO

ALTER TRIGGER Customers_Tracking_Delete
ON [dbo].[Customers] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Customers_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Customers_Tracking s JOIN DELETED i
    ON s.CustomerID = i.CustomerID
END

GO

ALTER PROCEDURE Sync.SP_Customers_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CustomerID],
		g.[CustomerName],g.[Birthdate],g.[Gender],g.[SSN],g.[SSNCreationDate],g.[SSNCreationLocationID],g.[BirthLocationID],g.[Address],g.[CurrentJob],g.[MilitaryStatusID],g.[SocialStatusID],g.[EducationalLevelID],g.[MilitaryPostpondingDate],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Customers] g RIGHT JOIN Sync.Customers_Tracking t  
    ON t.CustomerID = g.CustomerID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Customers_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Customers_Temp TABLE 
		(
			CustomerID UniqueIdentifier,
			[CustomerName] nvarchar(50),
				[Birthdate] datetime,
				[Gender] tinyint,
				[SSN] nvarchar(50),
				[SSNCreationDate] datetime,
				[SSNCreationLocationID] uniqueidentifier,
				[BirthLocationID] uniqueidentifier,
				[Address] nvarchar(MAX),
				[CurrentJob] nvarchar(50),
				[MilitaryStatusID] uniqueidentifier,
				[SocialStatusID] uniqueidentifier,
				[EducationalLevelID] uniqueidentifier,
				[MilitaryPostpondingDate] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Customers_Temp 
	   SELECT	Tbl.Col.value('CustomerID[1]','uniqueidentifier') CustomerID,
                Tbl.Col.value('CustomerName[1]', 'nvarchar(50)') [CustomerName],
Tbl.Col.value('Birthdate[1]', 'datetime') [Birthdate],
Tbl.Col.value('Gender[1]', 'tinyint') [Gender],
Tbl.Col.value('SSN[1]', 'nvarchar(50)') [SSN],
Tbl.Col.value('SSNCreationDate[1]', 'datetime') [SSNCreationDate],
Tbl.Col.value('SSNCreationLocationID[1]', 'uniqueidentifier') [SSNCreationLocationID],
Tbl.Col.value('BirthLocationID[1]', 'uniqueidentifier') [BirthLocationID],
Tbl.Col.value('Address[1]', 'nvarchar(MAX)') [Address],
Tbl.Col.value('CurrentJob[1]', 'nvarchar(50)') [CurrentJob],
Tbl.Col.value('MilitaryStatusID[1]', 'uniqueidentifier') [MilitaryStatusID],
Tbl.Col.value('SocialStatusID[1]', 'uniqueidentifier') [SocialStatusID],
Tbl.Col.value('EducationalLevelID[1]', 'uniqueidentifier') [EducationalLevelID],
Tbl.Col.value('MilitaryPostpondingDate[1]', 'datetime') [MilitaryPostpondingDate],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Customers') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Customers] ([CustomerID],[CustomerName],[Birthdate],[Gender],[SSN],[SSNCreationDate],[SSNCreationLocationID],[BirthLocationID],[Address],[CurrentJob],[MilitaryStatusID],[SocialStatusID],[EducationalLevelID],[MilitaryPostpondingDate])
	SELECT t.[CustomerID],t.[CustomerName],t.[Birthdate],t.[Gender],t.[SSN],t.[SSNCreationDate],t.[SSNCreationLocationID],t.[BirthLocationID],t.[Address],t.[CurrentJob],t.[MilitaryStatusID],t.[SocialStatusID],t.[EducationalLevelID],t.[MilitaryPostpondingDate]
	FROM  @Customers_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Customers_Tracking s
        WHERE t.CustomerID = s.CustomerID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Customers]
	SET [Customers].[CustomerName] = t.[CustomerName],[Customers].[Birthdate] = t.[Birthdate],[Customers].[Gender] = t.[Gender],[Customers].[SSN] = t.[SSN],[Customers].[SSNCreationDate] = t.[SSNCreationDate],[Customers].[SSNCreationLocationID] = t.[SSNCreationLocationID],[Customers].[BirthLocationID] = t.[BirthLocationID],[Customers].[Address] = t.[Address],[Customers].[CurrentJob] = t.[CurrentJob],[Customers].[MilitaryStatusID] = t.[MilitaryStatusID],[Customers].[SocialStatusID] = t.[SocialStatusID],[Customers].[EducationalLevelID] = t.[EducationalLevelID],[Customers].[MilitaryPostpondingDate] = t.[MilitaryPostpondingDate] 
	FROM [dbo].[Customers] s JOIN @Customers_Temp t 
    ON t.CustomerID = s.CustomerID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Customers_Tracking r
				  WHERE t.CustomerID = r.CustomerID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Customers]
    FROM @Customers_Temp t JOIN [dbo].[Customers] s
    ON t.CustomerID = s.CustomerID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CustomerPhones_Tracking_Insert
ON [dbo].[CustomerPhones] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CustomerPhones_Tracking ([PhoneID] ,IsDeleted,LastUpdatedDate)
	SELECT [PhoneID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CustomerPhones_Tracking_Update
ON [dbo].[CustomerPhones] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CustomerPhones_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CustomerPhones_Tracking s JOIN INSERTED i
    ON s.PhoneID = i.PhoneID
END

GO

ALTER TRIGGER CustomerPhones_Tracking_Delete
ON [dbo].[CustomerPhones] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CustomerPhones_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CustomerPhones_Tracking s JOIN DELETED i
    ON s.PhoneID = i.PhoneID
END

GO

ALTER PROCEDURE Sync.SP_CustomerPhones_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[PhoneID],
		g.[CustomerID],g.[PhoneType],g.[PhoneNumber],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CustomerPhones] g RIGHT JOIN Sync.CustomerPhones_Tracking t  
    ON t.PhoneID = g.PhoneID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CustomerPhones_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerPhones_Temp TABLE 
		(
			PhoneID UniqueIdentifier,
			[CustomerID] uniqueidentifier,
				[PhoneType] tinyint,
				[PhoneNumber] nchar(12),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerPhones_Temp 
	   SELECT	Tbl.Col.value('PhoneID[1]','uniqueidentifier') PhoneID,
                Tbl.Col.value('CustomerID[1]', 'uniqueidentifier') [CustomerID],
Tbl.Col.value('PhoneType[1]', 'tinyint') [PhoneType],
Tbl.Col.value('PhoneNumber[1]', 'nchar(12)') [PhoneNumber],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerPhones') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerPhones] ([PhoneID],[CustomerID],[PhoneType],[PhoneNumber])
	SELECT t.[PhoneID],t.[CustomerID],t.[PhoneType],t.[PhoneNumber]
	FROM  @CustomerPhones_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerPhones_Tracking s
        WHERE t.PhoneID = s.PhoneID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerPhones]
	SET [CustomerPhones].[CustomerID] = t.[CustomerID],[CustomerPhones].[PhoneType] = t.[PhoneType],[CustomerPhones].[PhoneNumber] = t.[PhoneNumber] 
	FROM [dbo].[CustomerPhones] s JOIN @CustomerPhones_Temp t 
    ON t.PhoneID = s.PhoneID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerPhones_Tracking r
				  WHERE t.PhoneID = r.PhoneID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerPhones]
    FROM @CustomerPhones_Temp t JOIN [dbo].[CustomerPhones] s
    ON t.PhoneID = s.PhoneID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Roles_Tracking_Insert
ON [dbo].[Roles] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Roles_Tracking ([RoleID] ,IsDeleted,LastUpdatedDate)
	SELECT [RoleID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Roles_Tracking_Update
ON [dbo].[Roles] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Roles_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Roles_Tracking s JOIN INSERTED i
    ON s.RoleID = i.RoleID
END

GO

ALTER TRIGGER Roles_Tracking_Delete
ON [dbo].[Roles] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Roles_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Roles_Tracking s JOIN DELETED i
    ON s.RoleID = i.RoleID
END

GO

ALTER PROCEDURE Sync.SP_Roles_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[RoleID],
		g.[RoleName],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Roles] g RIGHT JOIN Sync.Roles_Tracking t  
    ON t.RoleID = g.RoleID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Roles_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Roles_Temp TABLE 
		(
			RoleID UniqueIdentifier,
			[RoleName] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Roles_Temp 
	   SELECT	Tbl.Col.value('RoleID[1]','uniqueidentifier') RoleID,
                Tbl.Col.value('RoleName[1]', 'nvarchar(50)') [RoleName],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Roles') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Roles] ([RoleID],[RoleName])
	SELECT t.[RoleID],t.[RoleName]
	FROM  @Roles_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Roles_Tracking s
        WHERE t.RoleID = s.RoleID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Roles]
	SET [Roles].[RoleName] = t.[RoleName] 
	FROM [dbo].[Roles] s JOIN @Roles_Temp t 
    ON t.RoleID = s.RoleID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Roles_Tracking r
				  WHERE t.RoleID = r.RoleID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Roles]
    FROM @Roles_Temp t JOIN [dbo].[Roles] s
    ON t.RoleID = s.RoleID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Users_Tracking_Insert
ON [dbo].[Users] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Users_Tracking ([UserID] ,IsDeleted,LastUpdatedDate)
	SELECT [UserID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Users_Tracking_Update
ON [dbo].[Users] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Users_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Users_Tracking s JOIN INSERTED i
    ON s.UserID = i.UserID
END

GO

ALTER TRIGGER Users_Tracking_Delete
ON [dbo].[Users] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Users_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Users_Tracking s JOIN DELETED i
    ON s.UserID = i.UserID
END

GO

ALTER PROCEDURE Sync.SP_Users_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[UserID],
		g.[Username],g.[Password],g.[DisplayName],g.[Birthdate],g.[JobTitle],g.[Email],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Users] g RIGHT JOIN Sync.Users_Tracking t  
    ON t.UserID = g.UserID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Users_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Users_Temp TABLE 
		(
			UserID UniqueIdentifier,
			[Username] nvarchar(50),
				[Password] nvarchar(50),
				[DisplayName] nvarchar(50),
				[Birthdate] datetime,
				[JobTitle] nvarchar(50),
				[Email] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Users_Temp 
	   SELECT	Tbl.Col.value('UserID[1]','uniqueidentifier') UserID,
                Tbl.Col.value('Username[1]', 'nvarchar(50)') [Username],
Tbl.Col.value('Password[1]', 'nvarchar(50)') [Password],
Tbl.Col.value('DisplayName[1]', 'nvarchar(50)') [DisplayName],
Tbl.Col.value('Birthdate[1]', 'datetime') [Birthdate],
Tbl.Col.value('JobTitle[1]', 'nvarchar(50)') [JobTitle],
Tbl.Col.value('Email[1]', 'nvarchar(50)') [Email],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Users') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Users] ([UserID],[Username],[Password],[DisplayName],[Birthdate],[JobTitle],[Email])
	SELECT t.[UserID],t.[Username],t.[Password],t.[DisplayName],t.[Birthdate],t.[JobTitle],t.[Email]
	FROM  @Users_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Users_Tracking s
        WHERE t.UserID = s.UserID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Users]
	SET [Users].[Username] = t.[Username],[Users].[Password] = t.[Password],[Users].[DisplayName] = t.[DisplayName],[Users].[Birthdate] = t.[Birthdate],[Users].[JobTitle] = t.[JobTitle],[Users].[Email] = t.[Email] 
	FROM [dbo].[Users] s JOIN @Users_Temp t 
    ON t.UserID = s.UserID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Users_Tracking r
				  WHERE t.UserID = r.UserID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Users]
    FROM @Users_Temp t JOIN [dbo].[Users] s
    ON t.UserID = s.UserID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER UserMessages_Tracking_Insert
ON [dbo].[UserMessages] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.UserMessages_Tracking ([UserMessageID] ,IsDeleted,LastUpdatedDate)
	SELECT [UserMessageID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER UserMessages_Tracking_Update
ON [dbo].[UserMessages] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.UserMessages_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.UserMessages_Tracking s JOIN INSERTED i
    ON s.UserMessageID = i.UserMessageID
END

GO

ALTER TRIGGER UserMessages_Tracking_Delete
ON [dbo].[UserMessages] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.UserMessages_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.UserMessages_Tracking s JOIN DELETED i
    ON s.UserMessageID = i.UserMessageID
END

GO

ALTER PROCEDURE Sync.SP_UserMessages_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[UserMessageID],
		g.[CreationDate],g.[FromUserID],g.[ToUserID],g.[Subject],g.[Message],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[UserMessages] g RIGHT JOIN Sync.UserMessages_Tracking t  
    ON t.UserMessageID = g.UserMessageID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_UserMessages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @UserMessages_Temp TABLE 
		(
			UserMessageID UniqueIdentifier,
			[CreationDate] datetime,
				[FromUserID] uniqueidentifier,
				[ToUserID] uniqueidentifier,
				[Subject] nvarchar(255),
				[Message] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @UserMessages_Temp 
	   SELECT	Tbl.Col.value('UserMessageID[1]','uniqueidentifier') UserMessageID,
                Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('FromUserID[1]', 'uniqueidentifier') [FromUserID],
Tbl.Col.value('ToUserID[1]', 'uniqueidentifier') [ToUserID],
Tbl.Col.value('Subject[1]', 'nvarchar(255)') [Subject],
Tbl.Col.value('Message[1]', 'nvarchar(MAX)') [Message],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/UserMessages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[UserMessages] ([UserMessageID],[CreationDate],[FromUserID],[ToUserID],[Subject],[Message])
	SELECT t.[UserMessageID],t.[CreationDate],t.[FromUserID],t.[ToUserID],t.[Subject],t.[Message]
	FROM  @UserMessages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.UserMessages_Tracking s
        WHERE t.UserMessageID = s.UserMessageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[UserMessages]
	SET [UserMessages].[CreationDate] = t.[CreationDate],[UserMessages].[FromUserID] = t.[FromUserID],[UserMessages].[ToUserID] = t.[ToUserID],[UserMessages].[Subject] = t.[Subject],[UserMessages].[Message] = t.[Message] 
	FROM [dbo].[UserMessages] s JOIN @UserMessages_Temp t 
    ON t.UserMessageID = s.UserMessageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.UserMessages_Tracking r
				  WHERE t.UserMessageID = r.UserMessageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[UserMessages]
    FROM @UserMessages_Temp t JOIN [dbo].[UserMessages] s
    ON t.UserMessageID = s.UserMessageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER UsersRoles_Tracking_Insert
ON [dbo].[UsersRoles] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.UsersRoles_Tracking ([RoleID],[UserID] ,IsDeleted,LastUpdatedDate)
	SELECT [RoleID],[UserID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER UsersRoles_Tracking_Update
ON [dbo].[UsersRoles] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.UsersRoles_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.UsersRoles_Tracking s JOIN INSERTED i
    ON s.RoleID = i.RoleID AND s.UserID = i.UserID
END

GO

ALTER TRIGGER UsersRoles_Tracking_Delete
ON [dbo].[UsersRoles] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.UsersRoles_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.UsersRoles_Tracking s JOIN DELETED i
    ON s.RoleID = i.RoleID AND s.UserID = i.UserID
END

GO

ALTER PROCEDURE Sync.SP_UsersRoles_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[RoleID],t.[UserID],
		
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[UsersRoles] g RIGHT JOIN Sync.UsersRoles_Tracking t  
    ON t.RoleID = g.RoleID AND t.UserID = g.UserID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_UsersRoles_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @UsersRoles_Temp TABLE 
		(
			RoleID UniqueIdentifier,
	UserID UniqueIdentifier,
			
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @UsersRoles_Temp 
	   SELECT	Tbl.Col.value('RoleID[1]','uniqueidentifier') RoleID,
Tbl.Col.value('UserID[1]','uniqueidentifier') UserID,
                
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/UsersRoles') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[UsersRoles] ([RoleID],[UserID])
	SELECT t.[RoleID],t.[UserID]
	FROM  @UsersRoles_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.UsersRoles_Tracking s
        WHERE t.RoleID = s.RoleID AND t.UserID = s.UserID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[UsersRoles]
    FROM @UsersRoles_Temp t JOIN [dbo].[UsersRoles] s
    ON t.RoleID = s.RoleID AND t.UserID = s.UserID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER UsersCommittees_Tracking_Insert
ON [dbo].[UsersCommittees] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.UsersCommittees_Tracking ([CommitteeID],[UserID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeID],[UserID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER UsersCommittees_Tracking_Update
ON [dbo].[UsersCommittees] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.UsersCommittees_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.UsersCommittees_Tracking s JOIN INSERTED i
    ON s.CommitteeID = i.CommitteeID AND s.UserID = i.UserID
END

GO

ALTER TRIGGER UsersCommittees_Tracking_Delete
ON [dbo].[UsersCommittees] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.UsersCommittees_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.UsersCommittees_Tracking s JOIN DELETED i
    ON s.CommitteeID = i.CommitteeID AND s.UserID = i.UserID
END

GO

ALTER PROCEDURE Sync.SP_UsersCommittees_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeID],t.[UserID],
		
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[UsersCommittees] g RIGHT JOIN Sync.UsersCommittees_Tracking t  
    ON t.CommitteeID = g.CommitteeID AND t.UserID = g.UserID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_UsersCommittees_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @UsersCommittees_Temp TABLE 
		(
			CommitteeID UniqueIdentifier,
	UserID UniqueIdentifier,
			
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @UsersCommittees_Temp 
	   SELECT	Tbl.Col.value('CommitteeID[1]','uniqueidentifier') CommitteeID,
Tbl.Col.value('UserID[1]','uniqueidentifier') UserID,
                
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/UsersCommittees') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[UsersCommittees] ([CommitteeID],[UserID])
	SELECT t.[CommitteeID],t.[UserID]
	FROM  @UsersCommittees_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.UsersCommittees_Tracking s
        WHERE t.CommitteeID = s.CommitteeID AND t.UserID = s.UserID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[UsersCommittees]
    FROM @UsersCommittees_Temp t JOIN [dbo].[UsersCommittees] s
    ON t.CommitteeID = s.CommitteeID AND t.UserID = s.UserID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER UsersProjects_Tracking_Insert
ON [dbo].[UsersProjects] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.UsersProjects_Tracking ([ProjectID],[UserID] ,IsDeleted,LastUpdatedDate)
	SELECT [ProjectID],[UserID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER UsersProjects_Tracking_Update
ON [dbo].[UsersProjects] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.UsersProjects_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.UsersProjects_Tracking s JOIN INSERTED i
    ON s.ProjectID = i.ProjectID AND s.UserID = i.UserID
END

GO

ALTER TRIGGER UsersProjects_Tracking_Delete
ON [dbo].[UsersProjects] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.UsersProjects_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.UsersProjects_Tracking s JOIN DELETED i
    ON s.ProjectID = i.ProjectID AND s.UserID = i.UserID
END

GO

ALTER PROCEDURE Sync.SP_UsersProjects_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ProjectID],t.[UserID],
		
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[UsersProjects] g RIGHT JOIN Sync.UsersProjects_Tracking t  
    ON t.ProjectID = g.ProjectID AND t.UserID = g.UserID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_UsersProjects_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @UsersProjects_Temp TABLE 
		(
			ProjectID UniqueIdentifier,
	UserID UniqueIdentifier,
			
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @UsersProjects_Temp 
	   SELECT	Tbl.Col.value('ProjectID[1]','uniqueidentifier') ProjectID,
Tbl.Col.value('UserID[1]','uniqueidentifier') UserID,
                
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/UsersProjects') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[UsersProjects] ([ProjectID],[UserID])
	SELECT t.[ProjectID],t.[UserID]
	FROM  @UsersProjects_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.UsersProjects_Tracking s
        WHERE t.ProjectID = s.ProjectID AND t.UserID = s.UserID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[UsersProjects]
    FROM @UsersProjects_Temp t JOIN [dbo].[UsersProjects] s
    ON t.ProjectID = s.ProjectID AND t.UserID = s.UserID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Images_Tracking_Insert
ON [dbo].[Images] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Images_Tracking ([ImageID] ,IsDeleted,LastUpdatedDate)
	SELECT [ImageID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Images_Tracking_Update
ON [dbo].[Images] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Images_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Images_Tracking s JOIN INSERTED i
    ON s.ImageID = i.ImageID
END

GO

ALTER TRIGGER Images_Tracking_Delete
ON [dbo].[Images] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Images_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Images_Tracking s JOIN DELETED i
    ON s.ImageID = i.ImageID
END

GO

ALTER PROCEDURE Sync.SP_Images_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ImageID],
		g.[ImageTypeID],g.[ImageData],g.[Label],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Images] g RIGHT JOIN Sync.Images_Tracking t  
    ON t.ImageID = g.ImageID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Images_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Images_Temp TABLE 
		(
			ImageID UniqueIdentifier,
			[ImageTypeID] uniqueidentifier,
				[ImageData] varbinary,
				[Label] nvarchar(250),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Images_Temp 
	   SELECT	Tbl.Col.value('ImageID[1]','uniqueidentifier') ImageID,
                Tbl.Col.value('ImageTypeID[1]', 'uniqueidentifier') [ImageTypeID],
Tbl.Col.value('ImageData[1]', 'varbinary') [ImageData],
Tbl.Col.value('Label[1]', 'nvarchar(250)') [Label],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Images') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Images] ([ImageID],[ImageTypeID],[ImageData],[Label])
	SELECT t.[ImageID],t.[ImageTypeID],t.[ImageData],t.[Label]
	FROM  @Images_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Images_Tracking s
        WHERE t.ImageID = s.ImageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Images]
	SET [Images].[ImageTypeID] = t.[ImageTypeID],[Images].[ImageData] = t.[ImageData],[Images].[Label] = t.[Label] 
	FROM [dbo].[Images] s JOIN @Images_Temp t 
    ON t.ImageID = s.ImageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Images_Tracking r
				  WHERE t.ImageID = r.ImageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Images]
    FROM @Images_Temp t JOIN [dbo].[Images] s
    ON t.ImageID = s.ImageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Receipts_Tracking_Insert
ON [dbo].[Receipts] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Receipts_Tracking ([ReceiptID] ,IsDeleted,LastUpdatedDate)
	SELECT [ReceiptID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Receipts_Tracking_Update
ON [dbo].[Receipts] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Receipts_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Receipts_Tracking s JOIN INSERTED i
    ON s.ReceiptID = i.ReceiptID
END

GO

ALTER TRIGGER Receipts_Tracking_Delete
ON [dbo].[Receipts] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Receipts_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Receipts_Tracking s JOIN DELETED i
    ON s.ReceiptID = i.ReceiptID
END

GO

ALTER PROCEDURE Sync.SP_Receipts_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ReceiptID],
		g.[ReceiptNumber],g.[ReceiptDate],g.[ReceiptTypeID],g.[ReceiptAmount],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Receipts] g RIGHT JOIN Sync.Receipts_Tracking t  
    ON t.ReceiptID = g.ReceiptID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_Receipts_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Receipts_Temp TABLE 
		(
			ReceiptID UniqueIdentifier,
			[ReceiptNumber] bigint,
				[ReceiptDate] datetime,
				[ReceiptTypeID] uniqueidentifier,
				[ReceiptAmount] float,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Receipts_Temp 
	   SELECT	Tbl.Col.value('ReceiptID[1]','uniqueidentifier') ReceiptID,
                Tbl.Col.value('ReceiptNumber[1]', 'bigint') [ReceiptNumber],
Tbl.Col.value('ReceiptDate[1]', 'datetime') [ReceiptDate],
Tbl.Col.value('ReceiptTypeID[1]', 'uniqueidentifier') [ReceiptTypeID],
Tbl.Col.value('ReceiptAmount[1]', 'float') [ReceiptAmount],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Receipts') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Receipts] ([ReceiptID],[ReceiptNumber],[ReceiptDate],[ReceiptTypeID],[ReceiptAmount])
	SELECT t.[ReceiptID],t.[ReceiptNumber],t.[ReceiptDate],t.[ReceiptTypeID],t.[ReceiptAmount]
	FROM  @Receipts_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Receipts_Tracking s
        WHERE t.ReceiptID = s.ReceiptID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Receipts]
	SET [Receipts].[ReceiptNumber] = t.[ReceiptNumber],[Receipts].[ReceiptDate] = t.[ReceiptDate],[Receipts].[ReceiptTypeID] = t.[ReceiptTypeID],[Receipts].[ReceiptAmount] = t.[ReceiptAmount] 
	FROM [dbo].[Receipts] s JOIN @Receipts_Temp t 
    ON t.ReceiptID = s.ReceiptID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Receipts_Tracking r
				  WHERE t.ReceiptID = r.ReceiptID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Receipts]
    FROM @Receipts_Temp t JOIN [dbo].[Receipts] s
    ON t.ReceiptID = s.ReceiptID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CustomerLoans_Tracking_Insert
ON [dbo].[CustomerLoans] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CustomerLoans_Tracking ([LoanID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CustomerLoans_Tracking_Update
ON [dbo].[CustomerLoans] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CustomerLoans_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CustomerLoans_Tracking s JOIN INSERTED i
    ON s.LoanID = i.LoanID
END

GO

ALTER TRIGGER CustomerLoans_Tracking_Delete
ON [dbo].[CustomerLoans] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CustomerLoans_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CustomerLoans_Tracking s JOIN DELETED i
    ON s.LoanID = i.LoanID
END

GO

ALTER PROCEDURE Sync.SP_CustomerLoans_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanID],
		g.[LoanNumber],g.[CustomerID],g.[ProjectStageID],g.[CommitteeID],g.[LoanName],g.[LoanPlaceTypeID],g.[LoanAmount],g.[LoanPaymentPeriod],g.[LegalFormID],g.[LoanCreatedDate],g.[LoanTypeID],g.[LoanPaymentMethodID],g.[ResponsibleID],g.[IsLoanProjectExist],g.[RelationWithManagers],g.[ReceiptID],g.[FamilyCurrentDebts],g.[LoanAddress],g.[GuarantorID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CustomerLoans] g RIGHT JOIN Sync.CustomerLoans_Tracking t  
    ON t.LoanID = g.LoanID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_CustomerLoans_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerLoans_Temp TABLE 
		(
			LoanID UniqueIdentifier,
			[LoanNumber] int,
				[CustomerID] uniqueidentifier,
				[ProjectStageID] uniqueidentifier,
				[CommitteeID] uniqueidentifier,
				[LoanName] nvarchar(50),
				[LoanPlaceTypeID] uniqueidentifier,
				[LoanAmount] float,
				[LoanPaymentPeriod] int,
				[LegalFormID] uniqueidentifier,
				[LoanCreatedDate] datetime,
				[LoanTypeID] uniqueidentifier,
				[LoanPaymentMethodID] uniqueidentifier,
				[ResponsibleID] uniqueidentifier,
				[IsLoanProjectExist] bit,
				[RelationWithManagers] nvarchar(MAX),
				[ReceiptID] uniqueidentifier,
				[FamilyCurrentDebts] nvarchar(MAX),
				[LoanAddress] nvarchar(MAX),
				[GuarantorID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerLoans_Temp 
	   SELECT	Tbl.Col.value('LoanID[1]','uniqueidentifier') LoanID,
                Tbl.Col.value('LoanNumber[1]', 'int') [LoanNumber],
Tbl.Col.value('CustomerID[1]', 'uniqueidentifier') [CustomerID],
Tbl.Col.value('ProjectStageID[1]', 'uniqueidentifier') [ProjectStageID],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('LoanName[1]', 'nvarchar(50)') [LoanName],
Tbl.Col.value('LoanPlaceTypeID[1]', 'uniqueidentifier') [LoanPlaceTypeID],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPaymentPeriod[1]', 'int') [LoanPaymentPeriod],
Tbl.Col.value('LegalFormID[1]', 'uniqueidentifier') [LegalFormID],
Tbl.Col.value('LoanCreatedDate[1]', 'datetime') [LoanCreatedDate],
Tbl.Col.value('LoanTypeID[1]', 'uniqueidentifier') [LoanTypeID],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('ResponsibleID[1]', 'uniqueidentifier') [ResponsibleID],
Tbl.Col.value('IsLoanProjectExist[1]', 'bit') [IsLoanProjectExist],
Tbl.Col.value('RelationWithManagers[1]', 'nvarchar(MAX)') [RelationWithManagers],
Tbl.Col.value('ReceiptID[1]', 'uniqueidentifier') [ReceiptID],
Tbl.Col.value('FamilyCurrentDebts[1]', 'nvarchar(MAX)') [FamilyCurrentDebts],
Tbl.Col.value('LoanAddress[1]', 'nvarchar(MAX)') [LoanAddress],
Tbl.Col.value('GuarantorID[1]', 'uniqueidentifier') [GuarantorID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerLoans') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerLoans] ([LoanID],[LoanNumber],[CustomerID],[ProjectStageID],[CommitteeID],[LoanName],[LoanPlaceTypeID],[LoanAmount],[LoanPaymentPeriod],[LegalFormID],[LoanCreatedDate],[LoanTypeID],[LoanPaymentMethodID],[ResponsibleID],[IsLoanProjectExist],[RelationWithManagers],[ReceiptID],[FamilyCurrentDebts],[LoanAddress],[GuarantorID])
	SELECT t.[LoanID],t.[LoanNumber],t.[CustomerID],t.[ProjectStageID],t.[CommitteeID],t.[LoanName],t.[LoanPlaceTypeID],t.[LoanAmount],t.[LoanPaymentPeriod],t.[LegalFormID],t.[LoanCreatedDate],t.[LoanTypeID],t.[LoanPaymentMethodID],t.[ResponsibleID],t.[IsLoanProjectExist],t.[RelationWithManagers],t.[ReceiptID],t.[FamilyCurrentDebts],t.[LoanAddress],t.[GuarantorID]
	FROM  @CustomerLoans_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerLoans_Tracking s
        WHERE t.LoanID = s.LoanID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerLoans]
	SET [CustomerLoans].[LoanNumber] = t.[LoanNumber],[CustomerLoans].[CustomerID] = t.[CustomerID],[CustomerLoans].[ProjectStageID] = t.[ProjectStageID],[CustomerLoans].[CommitteeID] = t.[CommitteeID],[CustomerLoans].[LoanName] = t.[LoanName],[CustomerLoans].[LoanPlaceTypeID] = t.[LoanPlaceTypeID],[CustomerLoans].[LoanAmount] = t.[LoanAmount],[CustomerLoans].[LoanPaymentPeriod] = t.[LoanPaymentPeriod],[CustomerLoans].[LegalFormID] = t.[LegalFormID],[CustomerLoans].[LoanCreatedDate] = t.[LoanCreatedDate],[CustomerLoans].[LoanTypeID] = t.[LoanTypeID],[CustomerLoans].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[CustomerLoans].[ResponsibleID] = t.[ResponsibleID],[CustomerLoans].[IsLoanProjectExist] = t.[IsLoanProjectExist],[CustomerLoans].[RelationWithManagers] = t.[RelationWithManagers],[CustomerLoans].[ReceiptID] = t.[ReceiptID],[CustomerLoans].[FamilyCurrentDebts] = t.[FamilyCurrentDebts],[CustomerLoans].[LoanAddress] = t.[LoanAddress],[CustomerLoans].[GuarantorID] = t.[GuarantorID] 
	FROM [dbo].[CustomerLoans] s JOIN @CustomerLoans_Temp t 
    ON t.LoanID = s.LoanID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerLoans_Tracking r
				  WHERE t.LoanID = r.LoanID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerLoans]
    FROM @CustomerLoans_Temp t JOIN [dbo].[CustomerLoans] s
    ON t.LoanID = s.LoanID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CustomerLoanHistory_Tracking_Insert
ON [dbo].[CustomerLoanHistory] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CustomerLoanHistory_Tracking ([LoanHistoryID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanHistoryID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CustomerLoanHistory_Tracking_Update
ON [dbo].[CustomerLoanHistory] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CustomerLoanHistory_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CustomerLoanHistory_Tracking s JOIN INSERTED i
    ON s.LoanHistoryID = i.LoanHistoryID
END

GO

ALTER TRIGGER CustomerLoanHistory_Tracking_Delete
ON [dbo].[CustomerLoanHistory] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CustomerLoanHistory_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CustomerLoanHistory_Tracking s JOIN DELETED i
    ON s.LoanHistoryID = i.LoanHistoryID
END

GO

ALTER PROCEDURE Sync.SP_CustomerLoanHistory_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanHistoryID],
		g.[LoanID],g.[CustomerName],g.[LoanExecutionDate],g.[LoanProjectStageID],g.[OldCustomerCode],g.[LastConsecutiveDelay],g.[LastDiscreteDelay],g.[LoanCloseDate],g.[Notes],g.[LoanHistoryCloseTypeID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CustomerLoanHistory] g RIGHT JOIN Sync.CustomerLoanHistory_Tracking t  
    ON t.LoanHistoryID = g.LoanHistoryID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CustomerLoanHistory_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerLoanHistory_Temp TABLE 
		(
			LoanHistoryID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[CustomerName] nvarchar(50),
				[LoanExecutionDate] datetime,
				[LoanProjectStageID] uniqueidentifier,
				[OldCustomerCode] nvarchar(50),
				[LastConsecutiveDelay] int,
				[LastDiscreteDelay] int,
				[LoanCloseDate] datetime,
				[Notes] nvarchar(MAX),
				[LoanHistoryCloseTypeID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerLoanHistory_Temp 
	   SELECT	Tbl.Col.value('LoanHistoryID[1]','uniqueidentifier') LoanHistoryID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('CustomerName[1]', 'nvarchar(50)') [CustomerName],
Tbl.Col.value('LoanExecutionDate[1]', 'datetime') [LoanExecutionDate],
Tbl.Col.value('LoanProjectStageID[1]', 'uniqueidentifier') [LoanProjectStageID],
Tbl.Col.value('OldCustomerCode[1]', 'nvarchar(50)') [OldCustomerCode],
Tbl.Col.value('LastConsecutiveDelay[1]', 'int') [LastConsecutiveDelay],
Tbl.Col.value('LastDiscreteDelay[1]', 'int') [LastDiscreteDelay],
Tbl.Col.value('LoanCloseDate[1]', 'datetime') [LoanCloseDate],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
Tbl.Col.value('LoanHistoryCloseTypeID[1]', 'uniqueidentifier') [LoanHistoryCloseTypeID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerLoanHistory') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerLoanHistory] ([LoanHistoryID],[LoanID],[CustomerName],[LoanExecutionDate],[LoanProjectStageID],[OldCustomerCode],[LastConsecutiveDelay],[LastDiscreteDelay],[LoanCloseDate],[Notes],[LoanHistoryCloseTypeID])
	SELECT t.[LoanHistoryID],t.[LoanID],t.[CustomerName],t.[LoanExecutionDate],t.[LoanProjectStageID],t.[OldCustomerCode],t.[LastConsecutiveDelay],t.[LastDiscreteDelay],t.[LoanCloseDate],t.[Notes],t.[LoanHistoryCloseTypeID]
	FROM  @CustomerLoanHistory_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerLoanHistory_Tracking s
        WHERE t.LoanHistoryID = s.LoanHistoryID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerLoanHistory]
	SET [CustomerLoanHistory].[LoanID] = t.[LoanID],[CustomerLoanHistory].[CustomerName] = t.[CustomerName],[CustomerLoanHistory].[LoanExecutionDate] = t.[LoanExecutionDate],[CustomerLoanHistory].[LoanProjectStageID] = t.[LoanProjectStageID],[CustomerLoanHistory].[OldCustomerCode] = t.[OldCustomerCode],[CustomerLoanHistory].[LastConsecutiveDelay] = t.[LastConsecutiveDelay],[CustomerLoanHistory].[LastDiscreteDelay] = t.[LastDiscreteDelay],[CustomerLoanHistory].[LoanCloseDate] = t.[LoanCloseDate],[CustomerLoanHistory].[Notes] = t.[Notes],[CustomerLoanHistory].[LoanHistoryCloseTypeID] = t.[LoanHistoryCloseTypeID] 
	FROM [dbo].[CustomerLoanHistory] s JOIN @CustomerLoanHistory_Temp t 
    ON t.LoanHistoryID = s.LoanHistoryID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerLoanHistory_Tracking r
				  WHERE t.LoanHistoryID = r.LoanHistoryID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerLoanHistory]
    FROM @CustomerLoanHistory_Temp t JOIN [dbo].[CustomerLoanHistory] s
    ON t.LoanHistoryID = s.LoanHistoryID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanFeasabilityStudy_Tracking_Insert
ON [dbo].[LoanFeasabilityStudy] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanFeasabilityStudy_Tracking ([LoanFeasabilityStudyID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanFeasabilityStudyID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanFeasabilityStudy_Tracking_Update
ON [dbo].[LoanFeasabilityStudy] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanFeasabilityStudy_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanFeasabilityStudy_Tracking s JOIN INSERTED i
    ON s.LoanFeasabilityStudyID = i.LoanFeasabilityStudyID
END

GO

ALTER TRIGGER LoanFeasabilityStudy_Tracking_Delete
ON [dbo].[LoanFeasabilityStudy] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanFeasabilityStudy_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanFeasabilityStudy_Tracking s JOIN DELETED i
    ON s.LoanFeasabilityStudyID = i.LoanFeasabilityStudyID
END

GO

ALTER PROCEDURE Sync.SP_LoanFeasabilityStudy_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanFeasabilityStudyID],
		g.[LoanID],g.[FeasabilityStudyDate],g.[RevenueAmount],g.[CostAmount],g.[ProfitAmount],g.[ReturnOnInvestmentPercent],g.[LoanAmount],g.[LoanPaymentPeriod],g.[LoanPaymentMethodID],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanFeasabilityStudy] g RIGHT JOIN Sync.LoanFeasabilityStudy_Tracking t  
    ON t.LoanFeasabilityStudyID = g.LoanFeasabilityStudyID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanFeasabilityStudy_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanFeasabilityStudy_Temp TABLE 
		(
			LoanFeasabilityStudyID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[FeasabilityStudyDate] datetime,
				[RevenueAmount] float,
				[CostAmount] float,
				[ProfitAmount] float,
				[ReturnOnInvestmentPercent] decimal,
				[LoanAmount] float,
				[LoanPaymentPeriod] int,
				[LoanPaymentMethodID] uniqueidentifier,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanFeasabilityStudy_Temp 
	   SELECT	Tbl.Col.value('LoanFeasabilityStudyID[1]','uniqueidentifier') LoanFeasabilityStudyID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('FeasabilityStudyDate[1]', 'datetime') [FeasabilityStudyDate],
Tbl.Col.value('RevenueAmount[1]', 'float') [RevenueAmount],
Tbl.Col.value('CostAmount[1]', 'float') [CostAmount],
Tbl.Col.value('ProfitAmount[1]', 'float') [ProfitAmount],
Tbl.Col.value('ReturnOnInvestmentPercent[1]', 'decimal') [ReturnOnInvestmentPercent],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPaymentPeriod[1]', 'int') [LoanPaymentPeriod],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanFeasabilityStudy') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanFeasabilityStudy] ([LoanFeasabilityStudyID],[LoanID],[FeasabilityStudyDate],[RevenueAmount],[CostAmount],[ProfitAmount],[ReturnOnInvestmentPercent],[LoanAmount],[LoanPaymentPeriod],[LoanPaymentMethodID],[Notes])
	SELECT t.[LoanFeasabilityStudyID],t.[LoanID],t.[FeasabilityStudyDate],t.[RevenueAmount],t.[CostAmount],t.[ProfitAmount],t.[ReturnOnInvestmentPercent],t.[LoanAmount],t.[LoanPaymentPeriod],t.[LoanPaymentMethodID],t.[Notes]
	FROM  @LoanFeasabilityStudy_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanFeasabilityStudy_Tracking s
        WHERE t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanFeasabilityStudy]
	SET [LoanFeasabilityStudy].[LoanID] = t.[LoanID],[LoanFeasabilityStudy].[FeasabilityStudyDate] = t.[FeasabilityStudyDate],[LoanFeasabilityStudy].[RevenueAmount] = t.[RevenueAmount],[LoanFeasabilityStudy].[CostAmount] = t.[CostAmount],[LoanFeasabilityStudy].[ProfitAmount] = t.[ProfitAmount],[LoanFeasabilityStudy].[ReturnOnInvestmentPercent] = t.[ReturnOnInvestmentPercent],[LoanFeasabilityStudy].[LoanAmount] = t.[LoanAmount],[LoanFeasabilityStudy].[LoanPaymentPeriod] = t.[LoanPaymentPeriod],[LoanFeasabilityStudy].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[LoanFeasabilityStudy].[Notes] = t.[Notes] 
	FROM [dbo].[LoanFeasabilityStudy] s JOIN @LoanFeasabilityStudy_Temp t 
    ON t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanFeasabilityStudy_Tracking r
				  WHERE t.LoanFeasabilityStudyID = r.LoanFeasabilityStudyID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanFeasabilityStudy]
    FROM @LoanFeasabilityStudy_Temp t JOIN [dbo].[LoanFeasabilityStudy] s
    ON t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanFeasabilityStudyAttachments_Tracking_Insert
ON [dbo].[LoanFeasabilityStudyAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanFeasabilityStudyAttachments_Tracking ([LoanFeasabilityStudyAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanFeasabilityStudyAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanFeasabilityStudyAttachments_Tracking_Update
ON [dbo].[LoanFeasabilityStudyAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanFeasabilityStudyAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanFeasabilityStudyAttachments_Tracking s JOIN INSERTED i
    ON s.LoanFeasabilityStudyAttachmentID = i.LoanFeasabilityStudyAttachmentID
END

GO

ALTER TRIGGER LoanFeasabilityStudyAttachments_Tracking_Delete
ON [dbo].[LoanFeasabilityStudyAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanFeasabilityStudyAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanFeasabilityStudyAttachments_Tracking s JOIN DELETED i
    ON s.LoanFeasabilityStudyAttachmentID = i.LoanFeasabilityStudyAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanFeasabilityStudyAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanFeasabilityStudyAttachmentID],
		g.[LoanFeasabilityStudyID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanFeasabilityStudyAttachments] g RIGHT JOIN Sync.LoanFeasabilityStudyAttachments_Tracking t  
    ON t.LoanFeasabilityStudyAttachmentID = g.LoanFeasabilityStudyAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanFeasabilityStudyAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanFeasabilityStudyAttachments_Temp TABLE 
		(
			LoanFeasabilityStudyAttachmentID UniqueIdentifier,
			[LoanFeasabilityStudyID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanFeasabilityStudyAttachments_Temp 
	   SELECT	Tbl.Col.value('LoanFeasabilityStudyAttachmentID[1]','uniqueidentifier') LoanFeasabilityStudyAttachmentID,
                Tbl.Col.value('LoanFeasabilityStudyID[1]', 'uniqueidentifier') [LoanFeasabilityStudyID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanFeasabilityStudyAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanFeasabilityStudyAttachments] ([LoanFeasabilityStudyAttachmentID],[LoanFeasabilityStudyID],[Attachment])
	SELECT t.[LoanFeasabilityStudyAttachmentID],t.[LoanFeasabilityStudyID],t.[Attachment]
	FROM  @LoanFeasabilityStudyAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanFeasabilityStudyAttachments_Tracking s
        WHERE t.LoanFeasabilityStudyAttachmentID = s.LoanFeasabilityStudyAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanFeasabilityStudyAttachments]
	SET [LoanFeasabilityStudyAttachments].[LoanFeasabilityStudyID] = t.[LoanFeasabilityStudyID],[LoanFeasabilityStudyAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[LoanFeasabilityStudyAttachments] s JOIN @LoanFeasabilityStudyAttachments_Temp t 
    ON t.LoanFeasabilityStudyAttachmentID = s.LoanFeasabilityStudyAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanFeasabilityStudyAttachments_Tracking r
				  WHERE t.LoanFeasabilityStudyAttachmentID = r.LoanFeasabilityStudyAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanFeasabilityStudyAttachments]
    FROM @LoanFeasabilityStudyAttachments_Temp t JOIN [dbo].[LoanFeasabilityStudyAttachments] s
    ON t.LoanFeasabilityStudyAttachmentID = s.LoanFeasabilityStudyAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanSocialResearch_Tracking_Insert
ON [dbo].[LoanSocialResearch] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanSocialResearch_Tracking ([LoanSocialResearchID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanSocialResearchID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanSocialResearch_Tracking_Update
ON [dbo].[LoanSocialResearch] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanSocialResearch_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanSocialResearch_Tracking s JOIN INSERTED i
    ON s.LoanSocialResearchID = i.LoanSocialResearchID
END

GO

ALTER TRIGGER LoanSocialResearch_Tracking_Delete
ON [dbo].[LoanSocialResearch] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanSocialResearch_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanSocialResearch_Tracking s JOIN DELETED i
    ON s.LoanSocialResearchID = i.LoanSocialResearchID
END

GO

ALTER PROCEDURE Sync.SP_LoanSocialResearch_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanSocialResearchID],
		g.[LoanID],g.[SocialResearchDate],g.[FamilyNumbers],g.[IncomeAmount],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanSocialResearch] g RIGHT JOIN Sync.LoanSocialResearch_Tracking t  
    ON t.LoanSocialResearchID = g.LoanSocialResearchID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanSocialResearch_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanSocialResearch_Temp TABLE 
		(
			LoanSocialResearchID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[SocialResearchDate] datetime,
				[FamilyNumbers] int,
				[IncomeAmount] float,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanSocialResearch_Temp 
	   SELECT	Tbl.Col.value('LoanSocialResearchID[1]','uniqueidentifier') LoanSocialResearchID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('SocialResearchDate[1]', 'datetime') [SocialResearchDate],
Tbl.Col.value('FamilyNumbers[1]', 'int') [FamilyNumbers],
Tbl.Col.value('IncomeAmount[1]', 'float') [IncomeAmount],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanSocialResearch') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanSocialResearch] ([LoanSocialResearchID],[LoanID],[SocialResearchDate],[FamilyNumbers],[IncomeAmount],[Notes])
	SELECT t.[LoanSocialResearchID],t.[LoanID],t.[SocialResearchDate],t.[FamilyNumbers],t.[IncomeAmount],t.[Notes]
	FROM  @LoanSocialResearch_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanSocialResearch_Tracking s
        WHERE t.LoanSocialResearchID = s.LoanSocialResearchID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanSocialResearch]
	SET [LoanSocialResearch].[LoanID] = t.[LoanID],[LoanSocialResearch].[SocialResearchDate] = t.[SocialResearchDate],[LoanSocialResearch].[FamilyNumbers] = t.[FamilyNumbers],[LoanSocialResearch].[IncomeAmount] = t.[IncomeAmount],[LoanSocialResearch].[Notes] = t.[Notes] 
	FROM [dbo].[LoanSocialResearch] s JOIN @LoanSocialResearch_Temp t 
    ON t.LoanSocialResearchID = s.LoanSocialResearchID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanSocialResearch_Tracking r
				  WHERE t.LoanSocialResearchID = r.LoanSocialResearchID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanSocialResearch]
    FROM @LoanSocialResearch_Temp t JOIN [dbo].[LoanSocialResearch] s
    ON t.LoanSocialResearchID = s.LoanSocialResearchID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanSocialResearchAttachments_Tracking_Insert
ON [dbo].[LoanSocialResearchAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanSocialResearchAttachments_Tracking ([LoanSocialResearchAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanSocialResearchAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanSocialResearchAttachments_Tracking_Update
ON [dbo].[LoanSocialResearchAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanSocialResearchAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanSocialResearchAttachments_Tracking s JOIN INSERTED i
    ON s.LoanSocialResearchAttachmentID = i.LoanSocialResearchAttachmentID
END

GO

ALTER TRIGGER LoanSocialResearchAttachments_Tracking_Delete
ON [dbo].[LoanSocialResearchAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanSocialResearchAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanSocialResearchAttachments_Tracking s JOIN DELETED i
    ON s.LoanSocialResearchAttachmentID = i.LoanSocialResearchAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanSocialResearchAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanSocialResearchAttachmentID],
		g.[LoanSocialResearchID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanSocialResearchAttachments] g RIGHT JOIN Sync.LoanSocialResearchAttachments_Tracking t  
    ON t.LoanSocialResearchAttachmentID = g.LoanSocialResearchAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanSocialResearchAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanSocialResearchAttachments_Temp TABLE 
		(
			LoanSocialResearchAttachmentID UniqueIdentifier,
			[LoanSocialResearchID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanSocialResearchAttachments_Temp 
	   SELECT	Tbl.Col.value('LoanSocialResearchAttachmentID[1]','uniqueidentifier') LoanSocialResearchAttachmentID,
                Tbl.Col.value('LoanSocialResearchID[1]', 'uniqueidentifier') [LoanSocialResearchID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanSocialResearchAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanSocialResearchAttachments] ([LoanSocialResearchAttachmentID],[LoanSocialResearchID],[Attachment])
	SELECT t.[LoanSocialResearchAttachmentID],t.[LoanSocialResearchID],t.[Attachment]
	FROM  @LoanSocialResearchAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanSocialResearchAttachments_Tracking s
        WHERE t.LoanSocialResearchAttachmentID = s.LoanSocialResearchAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanSocialResearchAttachments]
	SET [LoanSocialResearchAttachments].[LoanSocialResearchID] = t.[LoanSocialResearchID],[LoanSocialResearchAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[LoanSocialResearchAttachments] s JOIN @LoanSocialResearchAttachments_Temp t 
    ON t.LoanSocialResearchAttachmentID = s.LoanSocialResearchAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanSocialResearchAttachments_Tracking r
				  WHERE t.LoanSocialResearchAttachmentID = r.LoanSocialResearchAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanSocialResearchAttachments]
    FROM @LoanSocialResearchAttachments_Temp t JOIN [dbo].[LoanSocialResearchAttachments] s
    ON t.LoanSocialResearchAttachmentID = s.LoanSocialResearchAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Board_Tracking_Insert
ON [dbo].[Board] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Board_Tracking ([BoardID] ,IsDeleted,LastUpdatedDate)
	SELECT [BoardID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Board_Tracking_Update
ON [dbo].[Board] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Board_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Board_Tracking s JOIN INSERTED i
    ON s.BoardID = i.BoardID
END

GO

ALTER TRIGGER Board_Tracking_Delete
ON [dbo].[Board] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Board_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Board_Tracking s JOIN DELETED i
    ON s.BoardID = i.BoardID
END

GO

ALTER PROCEDURE Sync.SP_Board_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[BoardID],
		g.[BoardNumber],g.[CommitteeID],g.[BoardDate],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Board] g RIGHT JOIN Sync.Board_Tracking t  
    ON t.BoardID = g.BoardID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_Board_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Board_Temp TABLE 
		(
			BoardID UniqueIdentifier,
			[BoardNumber] int,
				[CommitteeID] uniqueidentifier,
				[BoardDate] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Board_Temp 
	   SELECT	Tbl.Col.value('BoardID[1]','uniqueidentifier') BoardID,
                Tbl.Col.value('BoardNumber[1]', 'int') [BoardNumber],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('BoardDate[1]', 'datetime') [BoardDate],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Board') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Board] ([BoardID],[BoardNumber],[CommitteeID],[BoardDate])
	SELECT t.[BoardID],t.[BoardNumber],t.[CommitteeID],t.[BoardDate]
	FROM  @Board_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Board_Tracking s
        WHERE t.BoardID = s.BoardID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Board]
	SET [Board].[BoardNumber] = t.[BoardNumber],[Board].[CommitteeID] = t.[CommitteeID],[Board].[BoardDate] = t.[BoardDate] 
	FROM [dbo].[Board] s JOIN @Board_Temp t 
    ON t.BoardID = s.BoardID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Board_Tracking r
				  WHERE t.BoardID = r.BoardID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Board]
    FROM @Board_Temp t JOIN [dbo].[Board] s
    ON t.BoardID = s.BoardID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER BoardLoans_Tracking_Insert
ON [dbo].[BoardLoans] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.BoardLoans_Tracking ([BoardID],[LoanID] ,IsDeleted,LastUpdatedDate)
	SELECT [BoardID],[LoanID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER BoardLoans_Tracking_Update
ON [dbo].[BoardLoans] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.BoardLoans_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.BoardLoans_Tracking s JOIN INSERTED i
    ON s.BoardID = i.BoardID AND s.LoanID = i.LoanID
END

GO

ALTER TRIGGER BoardLoans_Tracking_Delete
ON [dbo].[BoardLoans] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.BoardLoans_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.BoardLoans_Tracking s JOIN DELETED i
    ON s.BoardID = i.BoardID AND s.LoanID = i.LoanID
END

GO

ALTER PROCEDURE Sync.SP_BoardLoans_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[BoardID],t.[LoanID],
		g.[EnteringNotes],g.[BoardLoanAmount],g.[BoardPaymentPeriod],g.[BoardpaymentMethodID],g.[StatusNotes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[BoardLoans] g RIGHT JOIN Sync.BoardLoans_Tracking t  
    ON t.BoardID = g.BoardID AND t.LoanID = g.LoanID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_BoardLoans_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @BoardLoans_Temp TABLE 
		(
			BoardID UniqueIdentifier,
	LoanID UniqueIdentifier,
			[EnteringNotes] nvarchar(500),
				[BoardLoanAmount] float,
				[BoardPaymentPeriod] int,
				[BoardpaymentMethodID] uniqueidentifier,
				[StatusNotes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @BoardLoans_Temp 
	   SELECT	Tbl.Col.value('BoardID[1]','uniqueidentifier') BoardID,
Tbl.Col.value('LoanID[1]','uniqueidentifier') LoanID,
                Tbl.Col.value('EnteringNotes[1]', 'nvarchar(500)') [EnteringNotes],
Tbl.Col.value('BoardLoanAmount[1]', 'float') [BoardLoanAmount],
Tbl.Col.value('BoardPaymentPeriod[1]', 'int') [BoardPaymentPeriod],
Tbl.Col.value('BoardpaymentMethodID[1]', 'uniqueidentifier') [BoardpaymentMethodID],
Tbl.Col.value('StatusNotes[1]', 'nvarchar(MAX)') [StatusNotes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/BoardLoans') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[BoardLoans] ([BoardID],[LoanID],[EnteringNotes],[BoardLoanAmount],[BoardPaymentPeriod],[BoardpaymentMethodID],[StatusNotes])
	SELECT t.[BoardID],t.[LoanID],t.[EnteringNotes],t.[BoardLoanAmount],t.[BoardPaymentPeriod],t.[BoardpaymentMethodID],t.[StatusNotes]
	FROM  @BoardLoans_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.BoardLoans_Tracking s
        WHERE t.BoardID = s.BoardID AND t.LoanID = s.LoanID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[BoardLoans]
	SET [BoardLoans].[EnteringNotes] = t.[EnteringNotes],[BoardLoans].[BoardLoanAmount] = t.[BoardLoanAmount],[BoardLoans].[BoardPaymentPeriod] = t.[BoardPaymentPeriod],[BoardLoans].[BoardpaymentMethodID] = t.[BoardpaymentMethodID],[BoardLoans].[StatusNotes] = t.[StatusNotes] 
	FROM [dbo].[BoardLoans] s JOIN @BoardLoans_Temp t 
    ON t.BoardID = s.BoardID AND t.LoanID = s.LoanID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.BoardLoans_Tracking r
				  WHERE t.BoardID = r.BoardID AND t.LoanID = r.LoanID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[BoardLoans]
    FROM @BoardLoans_Temp t JOIN [dbo].[BoardLoans] s
    ON t.BoardID = s.BoardID AND t.LoanID = s.LoanID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER BoardAttachments_Tracking_Insert
ON [dbo].[BoardAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.BoardAttachments_Tracking ([BoardAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [BoardAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER BoardAttachments_Tracking_Update
ON [dbo].[BoardAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.BoardAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.BoardAttachments_Tracking s JOIN INSERTED i
    ON s.BoardAttachmentID = i.BoardAttachmentID
END

GO

ALTER TRIGGER BoardAttachments_Tracking_Delete
ON [dbo].[BoardAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.BoardAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.BoardAttachments_Tracking s JOIN DELETED i
    ON s.BoardAttachmentID = i.BoardAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_BoardAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[BoardAttachmentID],
		g.[BoardID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[BoardAttachments] g RIGHT JOIN Sync.BoardAttachments_Tracking t  
    ON t.BoardAttachmentID = g.BoardAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_BoardAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @BoardAttachments_Temp TABLE 
		(
			BoardAttachmentID UniqueIdentifier,
			[BoardID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @BoardAttachments_Temp 
	   SELECT	Tbl.Col.value('BoardAttachmentID[1]','uniqueidentifier') BoardAttachmentID,
                Tbl.Col.value('BoardID[1]', 'uniqueidentifier') [BoardID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/BoardAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[BoardAttachments] ([BoardAttachmentID],[BoardID],[Attachment])
	SELECT t.[BoardAttachmentID],t.[BoardID],t.[Attachment]
	FROM  @BoardAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.BoardAttachments_Tracking s
        WHERE t.BoardAttachmentID = s.BoardAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[BoardAttachments]
	SET [BoardAttachments].[BoardID] = t.[BoardID],[BoardAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[BoardAttachments] s JOIN @BoardAttachments_Temp t 
    ON t.BoardAttachmentID = s.BoardAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.BoardAttachments_Tracking r
				  WHERE t.BoardAttachmentID = r.BoardAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[BoardAttachments]
    FROM @BoardAttachments_Temp t JOIN [dbo].[BoardAttachments] s
    ON t.BoardAttachmentID = s.BoardAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER BoardMembers_Tracking_Insert
ON [dbo].[BoardMembers] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.BoardMembers_Tracking ([BoardMemberID] ,IsDeleted,LastUpdatedDate)
	SELECT [BoardMemberID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER BoardMembers_Tracking_Update
ON [dbo].[BoardMembers] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.BoardMembers_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.BoardMembers_Tracking s JOIN INSERTED i
    ON s.BoardMemberID = i.BoardMemberID
END

GO

ALTER TRIGGER BoardMembers_Tracking_Delete
ON [dbo].[BoardMembers] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.BoardMembers_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.BoardMembers_Tracking s JOIN DELETED i
    ON s.BoardMemberID = i.BoardMemberID
END

GO

ALTER PROCEDURE Sync.SP_BoardMembers_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[BoardMemberID],
		g.[BoardMemberName],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[BoardMembers] g RIGHT JOIN Sync.BoardMembers_Tracking t  
    ON t.BoardMemberID = g.BoardMemberID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_BoardMembers_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @BoardMembers_Temp TABLE 
		(
			BoardMemberID UniqueIdentifier,
			[BoardMemberName] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @BoardMembers_Temp 
	   SELECT	Tbl.Col.value('BoardMemberID[1]','uniqueidentifier') BoardMemberID,
                Tbl.Col.value('BoardMemberName[1]', 'nvarchar(50)') [BoardMemberName],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/BoardMembers') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[BoardMembers] ([BoardMemberID],[BoardMemberName])
	SELECT t.[BoardMemberID],t.[BoardMemberName]
	FROM  @BoardMembers_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.BoardMembers_Tracking s
        WHERE t.BoardMemberID = s.BoardMemberID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[BoardMembers]
	SET [BoardMembers].[BoardMemberName] = t.[BoardMemberName] 
	FROM [dbo].[BoardMembers] s JOIN @BoardMembers_Temp t 
    ON t.BoardMemberID = s.BoardMemberID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.BoardMembers_Tracking r
				  WHERE t.BoardMemberID = r.BoardMemberID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[BoardMembers]
    FROM @BoardMembers_Temp t JOIN [dbo].[BoardMembers] s
    ON t.BoardMemberID = s.BoardMemberID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER BoardAttendees_Tracking_Insert
ON [dbo].[BoardAttendees] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.BoardAttendees_Tracking ([BoardAttendeesID] ,IsDeleted,LastUpdatedDate)
	SELECT [BoardAttendeesID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER BoardAttendees_Tracking_Update
ON [dbo].[BoardAttendees] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.BoardAttendees_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.BoardAttendees_Tracking s JOIN INSERTED i
    ON s.BoardAttendeesID = i.BoardAttendeesID
END

GO

ALTER TRIGGER BoardAttendees_Tracking_Delete
ON [dbo].[BoardAttendees] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.BoardAttendees_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.BoardAttendees_Tracking s JOIN DELETED i
    ON s.BoardAttendeesID = i.BoardAttendeesID
END

GO

ALTER PROCEDURE Sync.SP_BoardAttendees_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[BoardAttendeesID],
		g.[BoardID],g.[UserID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[BoardAttendees] g RIGHT JOIN Sync.BoardAttendees_Tracking t  
    ON t.BoardAttendeesID = g.BoardAttendeesID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_BoardAttendees_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @BoardAttendees_Temp TABLE 
		(
			BoardAttendeesID UniqueIdentifier,
			[BoardID] uniqueidentifier,
				[UserID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @BoardAttendees_Temp 
	   SELECT	Tbl.Col.value('BoardAttendeesID[1]','uniqueidentifier') BoardAttendeesID,
                Tbl.Col.value('BoardID[1]', 'uniqueidentifier') [BoardID],
Tbl.Col.value('UserID[1]', 'uniqueidentifier') [UserID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/BoardAttendees') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[BoardAttendees] ([BoardAttendeesID],[BoardID],[UserID])
	SELECT t.[BoardAttendeesID],t.[BoardID],t.[UserID]
	FROM  @BoardAttendees_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.BoardAttendees_Tracking s
        WHERE t.BoardAttendeesID = s.BoardAttendeesID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[BoardAttendees]
	SET [BoardAttendees].[BoardID] = t.[BoardID],[BoardAttendees].[UserID] = t.[UserID] 
	FROM [dbo].[BoardAttendees] s JOIN @BoardAttendees_Temp t 
    ON t.BoardAttendeesID = s.BoardAttendeesID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.BoardAttendees_Tracking r
				  WHERE t.BoardAttendeesID = r.BoardAttendeesID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[BoardAttendees]
    FROM @BoardAttendees_Temp t JOIN [dbo].[BoardAttendees] s
    ON t.BoardAttendeesID = s.BoardAttendeesID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER wfProcessInstances_Tracking_Insert
ON [dbo].[wfProcessInstances] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.wfProcessInstances_Tracking ([ProcessInstanceID] ,IsDeleted,LastUpdatedDate)
	SELECT [ProcessInstanceID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER wfProcessInstances_Tracking_Update
ON [dbo].[wfProcessInstances] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.wfProcessInstances_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.wfProcessInstances_Tracking s JOIN INSERTED i
    ON s.ProcessInstanceID = i.ProcessInstanceID
END

GO

ALTER TRIGGER wfProcessInstances_Tracking_Delete
ON [dbo].[wfProcessInstances] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.wfProcessInstances_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.wfProcessInstances_Tracking s JOIN DELETED i
    ON s.ProcessInstanceID = i.ProcessInstanceID
END

GO

ALTER PROCEDURE Sync.SP_wfProcessInstances_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[ProcessInstanceID],
		g.[ProcessID],g.[UserID],g.[CreationDate],g.[CompletionDate],g.[EntityID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[wfProcessInstances] g RIGHT JOIN Sync.wfProcessInstances_Tracking t  
    ON t.ProcessInstanceID = g.ProcessInstanceID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_wfProcessInstances_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @wfProcessInstances_Temp TABLE 
		(
			ProcessInstanceID UniqueIdentifier,
			[ProcessID] uniqueidentifier,
				[UserID] uniqueidentifier,
				[CreationDate] datetime,
				[CompletionDate] datetime,
				[EntityID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @wfProcessInstances_Temp 
	   SELECT	Tbl.Col.value('ProcessInstanceID[1]','uniqueidentifier') ProcessInstanceID,
                Tbl.Col.value('ProcessID[1]', 'uniqueidentifier') [ProcessID],
Tbl.Col.value('UserID[1]', 'uniqueidentifier') [UserID],
Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('CompletionDate[1]', 'datetime') [CompletionDate],
Tbl.Col.value('EntityID[1]', 'uniqueidentifier') [EntityID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/wfProcessInstances') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[wfProcessInstances] ([ProcessInstanceID],[ProcessID],[UserID],[CreationDate],[CompletionDate],[EntityID])
	SELECT t.[ProcessInstanceID],t.[ProcessID],t.[UserID],t.[CreationDate],t.[CompletionDate],t.[EntityID]
	FROM  @wfProcessInstances_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.wfProcessInstances_Tracking s
        WHERE t.ProcessInstanceID = s.ProcessInstanceID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[wfProcessInstances]
	SET [wfProcessInstances].[ProcessID] = t.[ProcessID],[wfProcessInstances].[UserID] = t.[UserID],[wfProcessInstances].[CreationDate] = t.[CreationDate],[wfProcessInstances].[CompletionDate] = t.[CompletionDate],[wfProcessInstances].[EntityID] = t.[EntityID] 
	FROM [dbo].[wfProcessInstances] s JOIN @wfProcessInstances_Temp t 
    ON t.ProcessInstanceID = s.ProcessInstanceID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.wfProcessInstances_Tracking r
				  WHERE t.ProcessInstanceID = r.ProcessInstanceID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[wfProcessInstances]
    FROM @wfProcessInstances_Temp t JOIN [dbo].[wfProcessInstances] s
    ON t.ProcessInstanceID = s.ProcessInstanceID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER wfStepInstances_Tracking_Insert
ON [dbo].[wfStepInstances] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.wfStepInstances_Tracking ([StepInstanceID] ,IsDeleted,LastUpdatedDate)
	SELECT [StepInstanceID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER wfStepInstances_Tracking_Update
ON [dbo].[wfStepInstances] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.wfStepInstances_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.wfStepInstances_Tracking s JOIN INSERTED i
    ON s.StepInstanceID = i.StepInstanceID
END

GO

ALTER TRIGGER wfStepInstances_Tracking_Delete
ON [dbo].[wfStepInstances] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.wfStepInstances_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.wfStepInstances_Tracking s JOIN DELETED i
    ON s.StepInstanceID = i.StepInstanceID
END

GO

ALTER PROCEDURE Sync.SP_wfStepInstances_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[StepInstanceID],
		g.[ProcessInstanceID],g.[StepID],g.[CreationDate],g.[CompletionDate],g.[UserID],g.[Comments],g.[ActionTypeID],g.[PreviousStepInstanceID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[wfStepInstances] g RIGHT JOIN Sync.wfStepInstances_Tracking t  
    ON t.StepInstanceID = g.StepInstanceID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_wfStepInstances_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @wfStepInstances_Temp TABLE 
		(
			StepInstanceID UniqueIdentifier,
			[ProcessInstanceID] uniqueidentifier,
				[StepID] uniqueidentifier,
				[CreationDate] datetime,
				[CompletionDate] datetime,
				[UserID] uniqueidentifier,
				[Comments] nvarchar(MAX),
				[ActionTypeID] uniqueidentifier,
				[PreviousStepInstanceID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @wfStepInstances_Temp 
	   SELECT	Tbl.Col.value('StepInstanceID[1]','uniqueidentifier') StepInstanceID,
                Tbl.Col.value('ProcessInstanceID[1]', 'uniqueidentifier') [ProcessInstanceID],
Tbl.Col.value('StepID[1]', 'uniqueidentifier') [StepID],
Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('CompletionDate[1]', 'datetime') [CompletionDate],
Tbl.Col.value('UserID[1]', 'uniqueidentifier') [UserID],
Tbl.Col.value('Comments[1]', 'nvarchar(MAX)') [Comments],
Tbl.Col.value('ActionTypeID[1]', 'uniqueidentifier') [ActionTypeID],
Tbl.Col.value('PreviousStepInstanceID[1]', 'uniqueidentifier') [PreviousStepInstanceID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/wfStepInstances') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[wfStepInstances] ([StepInstanceID],[ProcessInstanceID],[StepID],[CreationDate],[CompletionDate],[UserID],[Comments],[ActionTypeID],[PreviousStepInstanceID])
	SELECT t.[StepInstanceID],t.[ProcessInstanceID],t.[StepID],t.[CreationDate],t.[CompletionDate],t.[UserID],t.[Comments],t.[ActionTypeID],t.[PreviousStepInstanceID]
	FROM  @wfStepInstances_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.wfStepInstances_Tracking s
        WHERE t.StepInstanceID = s.StepInstanceID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[wfStepInstances]
	SET [wfStepInstances].[ProcessInstanceID] = t.[ProcessInstanceID],[wfStepInstances].[StepID] = t.[StepID],[wfStepInstances].[CreationDate] = t.[CreationDate],[wfStepInstances].[CompletionDate] = t.[CompletionDate],[wfStepInstances].[UserID] = t.[UserID],[wfStepInstances].[Comments] = t.[Comments],[wfStepInstances].[ActionTypeID] = t.[ActionTypeID],[wfStepInstances].[PreviousStepInstanceID] = t.[PreviousStepInstanceID] 
	FROM [dbo].[wfStepInstances] s JOIN @wfStepInstances_Temp t 
    ON t.StepInstanceID = s.StepInstanceID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.wfStepInstances_Tracking r
				  WHERE t.StepInstanceID = r.StepInstanceID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[wfStepInstances]
    FROM @wfStepInstances_Temp t JOIN [dbo].[wfStepInstances] s
    ON t.StepInstanceID = s.StepInstanceID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccount_Tracking_Insert
ON [dbo].[CommitteeBankAccount] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccount_Tracking ([CommitteeBankAccountID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccount_Tracking_Update
ON [dbo].[CommitteeBankAccount] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccount_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccount_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountID = i.CommitteeBankAccountID
END

GO

ALTER TRIGGER CommitteeBankAccount_Tracking_Delete
ON [dbo].[CommitteeBankAccount] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccount_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccount_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountID = i.CommitteeBankAccountID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccount_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountID],
		g.[CommitteeID],g.[BankAccountNumber],g.[ProjectID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccount] g RIGHT JOIN Sync.CommitteeBankAccount_Tracking t  
    ON t.CommitteeBankAccountID = g.CommitteeBankAccountID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccount_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccount_Temp TABLE 
		(
			CommitteeBankAccountID UniqueIdentifier,
			[CommitteeID] uniqueidentifier,
				[BankAccountNumber] nvarchar(20),
				[ProjectID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccount_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountID[1]','uniqueidentifier') CommitteeBankAccountID,
                Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('BankAccountNumber[1]', 'nvarchar(20)') [BankAccountNumber],
Tbl.Col.value('ProjectID[1]', 'uniqueidentifier') [ProjectID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccount') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccount] ([CommitteeBankAccountID],[CommitteeID],[BankAccountNumber],[ProjectID])
	SELECT t.[CommitteeBankAccountID],t.[CommitteeID],t.[BankAccountNumber],t.[ProjectID]
	FROM  @CommitteeBankAccount_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccount_Tracking s
        WHERE t.CommitteeBankAccountID = s.CommitteeBankAccountID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccount]
	SET [CommitteeBankAccount].[CommitteeID] = t.[CommitteeID],[CommitteeBankAccount].[BankAccountNumber] = t.[BankAccountNumber],[CommitteeBankAccount].[ProjectID] = t.[ProjectID] 
	FROM [dbo].[CommitteeBankAccount] s JOIN @CommitteeBankAccount_Temp t 
    ON t.CommitteeBankAccountID = s.CommitteeBankAccountID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccount_Tracking r
				  WHERE t.CommitteeBankAccountID = r.CommitteeBankAccountID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccount]
    FROM @CommitteeBankAccount_Temp t JOIN [dbo].[CommitteeBankAccount] s
    ON t.CommitteeBankAccountID = s.CommitteeBankAccountID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountDeposites_Tracking_Insert
ON [dbo].[CommitteeBankAccountDeposites] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountDeposites_Tracking ([CommitteeBankAccountDepositID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountDepositID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountDeposites_Tracking_Update
ON [dbo].[CommitteeBankAccountDeposites] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDeposites_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountDeposites_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountDepositID = i.CommitteeBankAccountDepositID
END

GO

ALTER TRIGGER CommitteeBankAccountDeposites_Tracking_Delete
ON [dbo].[CommitteeBankAccountDeposites] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDeposites_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountDeposites_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountDepositID = i.CommitteeBankAccountDepositID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDeposites_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountDepositID],
		g.[CommitteeBankAccountID],g.[DepositDate],g.[OperationNumber],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountDeposites] g RIGHT JOIN Sync.CommitteeBankAccountDeposites_Tracking t  
    ON t.CommitteeBankAccountDepositID = g.CommitteeBankAccountDepositID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDeposites_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountDeposites_Temp TABLE 
		(
			CommitteeBankAccountDepositID UniqueIdentifier,
			[CommitteeBankAccountID] uniqueidentifier,
				[DepositDate] datetime,
				[OperationNumber] nvarchar(50),
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountDeposites_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountDepositID[1]','uniqueidentifier') CommitteeBankAccountDepositID,
                Tbl.Col.value('CommitteeBankAccountID[1]', 'uniqueidentifier') [CommitteeBankAccountID],
Tbl.Col.value('DepositDate[1]', 'datetime') [DepositDate],
Tbl.Col.value('OperationNumber[1]', 'nvarchar(50)') [OperationNumber],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountDeposites') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountDeposites] ([CommitteeBankAccountDepositID],[CommitteeBankAccountID],[DepositDate],[OperationNumber],[Notes])
	SELECT t.[CommitteeBankAccountDepositID],t.[CommitteeBankAccountID],t.[DepositDate],t.[OperationNumber],t.[Notes]
	FROM  @CommitteeBankAccountDeposites_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountDeposites_Tracking s
        WHERE t.CommitteeBankAccountDepositID = s.CommitteeBankAccountDepositID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountDeposites]
	SET [CommitteeBankAccountDeposites].[CommitteeBankAccountID] = t.[CommitteeBankAccountID],[CommitteeBankAccountDeposites].[DepositDate] = t.[DepositDate],[CommitteeBankAccountDeposites].[OperationNumber] = t.[OperationNumber],[CommitteeBankAccountDeposites].[Notes] = t.[Notes] 
	FROM [dbo].[CommitteeBankAccountDeposites] s JOIN @CommitteeBankAccountDeposites_Temp t 
    ON t.CommitteeBankAccountDepositID = s.CommitteeBankAccountDepositID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountDeposites_Tracking r
				  WHERE t.CommitteeBankAccountDepositID = r.CommitteeBankAccountDepositID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountDeposites]
    FROM @CommitteeBankAccountDeposites_Temp t JOIN [dbo].[CommitteeBankAccountDeposites] s
    ON t.CommitteeBankAccountDepositID = s.CommitteeBankAccountDepositID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountDepositReceipts_Tracking_Insert
ON [dbo].[CommitteeBankAccountDepositReceipts] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountDepositReceipts_Tracking ([CommitteeBankAccountDepositReceiptID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountDepositReceiptID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountDepositReceipts_Tracking_Update
ON [dbo].[CommitteeBankAccountDepositReceipts] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDepositReceipts_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountDepositReceipts_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountDepositReceiptID = i.CommitteeBankAccountDepositReceiptID
END

GO

ALTER TRIGGER CommitteeBankAccountDepositReceipts_Tracking_Delete
ON [dbo].[CommitteeBankAccountDepositReceipts] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDepositReceipts_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountDepositReceipts_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountDepositReceiptID = i.CommitteeBankAccountDepositReceiptID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDepositReceipts_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountDepositReceiptID],
		g.[CommitteeBankAccountDepositID],g.[ReceiptID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountDepositReceipts] g RIGHT JOIN Sync.CommitteeBankAccountDepositReceipts_Tracking t  
    ON t.CommitteeBankAccountDepositReceiptID = g.CommitteeBankAccountDepositReceiptID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDepositReceipts_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountDepositReceipts_Temp TABLE 
		(
			CommitteeBankAccountDepositReceiptID UniqueIdentifier,
			[CommitteeBankAccountDepositID] uniqueidentifier,
				[ReceiptID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountDepositReceipts_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountDepositReceiptID[1]','uniqueidentifier') CommitteeBankAccountDepositReceiptID,
                Tbl.Col.value('CommitteeBankAccountDepositID[1]', 'uniqueidentifier') [CommitteeBankAccountDepositID],
Tbl.Col.value('ReceiptID[1]', 'uniqueidentifier') [ReceiptID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountDepositReceipts') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountDepositReceipts] ([CommitteeBankAccountDepositReceiptID],[CommitteeBankAccountDepositID],[ReceiptID])
	SELECT t.[CommitteeBankAccountDepositReceiptID],t.[CommitteeBankAccountDepositID],t.[ReceiptID]
	FROM  @CommitteeBankAccountDepositReceipts_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountDepositReceipts_Tracking s
        WHERE t.CommitteeBankAccountDepositReceiptID = s.CommitteeBankAccountDepositReceiptID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountDepositReceipts]
	SET [CommitteeBankAccountDepositReceipts].[CommitteeBankAccountDepositID] = t.[CommitteeBankAccountDepositID],[CommitteeBankAccountDepositReceipts].[ReceiptID] = t.[ReceiptID] 
	FROM [dbo].[CommitteeBankAccountDepositReceipts] s JOIN @CommitteeBankAccountDepositReceipts_Temp t 
    ON t.CommitteeBankAccountDepositReceiptID = s.CommitteeBankAccountDepositReceiptID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountDepositReceipts_Tracking r
				  WHERE t.CommitteeBankAccountDepositReceiptID = r.CommitteeBankAccountDepositReceiptID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountDepositReceipts]
    FROM @CommitteeBankAccountDepositReceipts_Temp t JOIN [dbo].[CommitteeBankAccountDepositReceipts] s
    ON t.CommitteeBankAccountDepositReceiptID = s.CommitteeBankAccountDepositReceiptID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountDepositAttachments_Tracking_Insert
ON [dbo].[CommitteeBankAccountDepositAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountDepositAttachments_Tracking ([CommitteeBankAccountDepositAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountDepositAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountDepositAttachments_Tracking_Update
ON [dbo].[CommitteeBankAccountDepositAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDepositAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountDepositAttachments_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountDepositAttachmentID = i.CommitteeBankAccountDepositAttachmentID
END

GO

ALTER TRIGGER CommitteeBankAccountDepositAttachments_Tracking_Delete
ON [dbo].[CommitteeBankAccountDepositAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountDepositAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountDepositAttachments_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountDepositAttachmentID = i.CommitteeBankAccountDepositAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDepositAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountDepositAttachmentID],
		g.[CommitteeBankAccountDepositID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountDepositAttachments] g RIGHT JOIN Sync.CommitteeBankAccountDepositAttachments_Tracking t  
    ON t.CommitteeBankAccountDepositAttachmentID = g.CommitteeBankAccountDepositAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountDepositAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountDepositAttachments_Temp TABLE 
		(
			CommitteeBankAccountDepositAttachmentID UniqueIdentifier,
			[CommitteeBankAccountDepositID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountDepositAttachments_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountDepositAttachmentID[1]','uniqueidentifier') CommitteeBankAccountDepositAttachmentID,
                Tbl.Col.value('CommitteeBankAccountDepositID[1]', 'uniqueidentifier') [CommitteeBankAccountDepositID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountDepositAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountDepositAttachments] ([CommitteeBankAccountDepositAttachmentID],[CommitteeBankAccountDepositID],[Attachment])
	SELECT t.[CommitteeBankAccountDepositAttachmentID],t.[CommitteeBankAccountDepositID],t.[Attachment]
	FROM  @CommitteeBankAccountDepositAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountDepositAttachments_Tracking s
        WHERE t.CommitteeBankAccountDepositAttachmentID = s.CommitteeBankAccountDepositAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountDepositAttachments]
	SET [CommitteeBankAccountDepositAttachments].[CommitteeBankAccountDepositID] = t.[CommitteeBankAccountDepositID],[CommitteeBankAccountDepositAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[CommitteeBankAccountDepositAttachments] s JOIN @CommitteeBankAccountDepositAttachments_Temp t 
    ON t.CommitteeBankAccountDepositAttachmentID = s.CommitteeBankAccountDepositAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountDepositAttachments_Tracking r
				  WHERE t.CommitteeBankAccountDepositAttachmentID = r.CommitteeBankAccountDepositAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountDepositAttachments]
    FROM @CommitteeBankAccountDepositAttachments_Temp t JOIN [dbo].[CommitteeBankAccountDepositAttachments] s
    ON t.CommitteeBankAccountDepositAttachmentID = s.CommitteeBankAccountDepositAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountExpenses_Tracking_Insert
ON [dbo].[CommitteeBankAccountExpenses] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountExpenses_Tracking ([CommitteeBankAccountExpensesID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountExpensesID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountExpenses_Tracking_Update
ON [dbo].[CommitteeBankAccountExpenses] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountExpenses_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountExpenses_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountExpensesID = i.CommitteeBankAccountExpensesID
END

GO

ALTER TRIGGER CommitteeBankAccountExpenses_Tracking_Delete
ON [dbo].[CommitteeBankAccountExpenses] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountExpenses_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountExpenses_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountExpensesID = i.CommitteeBankAccountExpensesID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountExpenses_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountExpensesID],
		g.[CommitteeBankAccountID],g.[WithdrawDate],g.[WithdrawAmount],g.[WithdrawCheckNumber],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountExpenses] g RIGHT JOIN Sync.CommitteeBankAccountExpenses_Tracking t  
    ON t.CommitteeBankAccountExpensesID = g.CommitteeBankAccountExpensesID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountExpenses_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountExpenses_Temp TABLE 
		(
			CommitteeBankAccountExpensesID UniqueIdentifier,
			[CommitteeBankAccountID] uniqueidentifier,
				[WithdrawDate] datetime,
				[WithdrawAmount] float,
				[WithdrawCheckNumber] nvarchar(50),
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountExpenses_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountExpensesID[1]','uniqueidentifier') CommitteeBankAccountExpensesID,
                Tbl.Col.value('CommitteeBankAccountID[1]', 'uniqueidentifier') [CommitteeBankAccountID],
Tbl.Col.value('WithdrawDate[1]', 'datetime') [WithdrawDate],
Tbl.Col.value('WithdrawAmount[1]', 'float') [WithdrawAmount],
Tbl.Col.value('WithdrawCheckNumber[1]', 'nvarchar(50)') [WithdrawCheckNumber],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountExpenses') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountExpenses] ([CommitteeBankAccountExpensesID],[CommitteeBankAccountID],[WithdrawDate],[WithdrawAmount],[WithdrawCheckNumber],[Notes])
	SELECT t.[CommitteeBankAccountExpensesID],t.[CommitteeBankAccountID],t.[WithdrawDate],t.[WithdrawAmount],t.[WithdrawCheckNumber],t.[Notes]
	FROM  @CommitteeBankAccountExpenses_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountExpenses_Tracking s
        WHERE t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountExpenses]
	SET [CommitteeBankAccountExpenses].[CommitteeBankAccountID] = t.[CommitteeBankAccountID],[CommitteeBankAccountExpenses].[WithdrawDate] = t.[WithdrawDate],[CommitteeBankAccountExpenses].[WithdrawAmount] = t.[WithdrawAmount],[CommitteeBankAccountExpenses].[WithdrawCheckNumber] = t.[WithdrawCheckNumber],[CommitteeBankAccountExpenses].[Notes] = t.[Notes] 
	FROM [dbo].[CommitteeBankAccountExpenses] s JOIN @CommitteeBankAccountExpenses_Temp t 
    ON t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountExpenses_Tracking r
				  WHERE t.CommitteeBankAccountExpensesID = r.CommitteeBankAccountExpensesID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountExpenses]
    FROM @CommitteeBankAccountExpenses_Temp t JOIN [dbo].[CommitteeBankAccountExpenses] s
    ON t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountMainBranchDeposites_Tracking_Insert
ON [dbo].[CommitteeBankAccountMainBranchDeposites] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountMainBranchDeposites_Tracking ([CommitteeBankAccountMainBranchDepositID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountMainBranchDepositID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountMainBranchDeposites_Tracking_Update
ON [dbo].[CommitteeBankAccountMainBranchDeposites] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountMainBranchDeposites_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountMainBranchDeposites_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountMainBranchDepositID = i.CommitteeBankAccountMainBranchDepositID
END

GO

ALTER TRIGGER CommitteeBankAccountMainBranchDeposites_Tracking_Delete
ON [dbo].[CommitteeBankAccountMainBranchDeposites] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountMainBranchDeposites_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountMainBranchDeposites_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountMainBranchDepositID = i.CommitteeBankAccountMainBranchDepositID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDeposites_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountMainBranchDepositID],
		g.[CommitteeBankAccountID],g.[DepositDate],g.[DepositAmount],g.[OperationNumber],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountMainBranchDeposites] g RIGHT JOIN Sync.CommitteeBankAccountMainBranchDeposites_Tracking t  
    ON t.CommitteeBankAccountMainBranchDepositID = g.CommitteeBankAccountMainBranchDepositID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDeposites_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountMainBranchDeposites_Temp TABLE 
		(
			CommitteeBankAccountMainBranchDepositID UniqueIdentifier,
			[CommitteeBankAccountID] uniqueidentifier,
				[DepositDate] datetime,
				[DepositAmount] float,
				[OperationNumber] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountMainBranchDeposites_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountMainBranchDepositID[1]','uniqueidentifier') CommitteeBankAccountMainBranchDepositID,
                Tbl.Col.value('CommitteeBankAccountID[1]', 'uniqueidentifier') [CommitteeBankAccountID],
Tbl.Col.value('DepositDate[1]', 'datetime') [DepositDate],
Tbl.Col.value('DepositAmount[1]', 'float') [DepositAmount],
Tbl.Col.value('OperationNumber[1]', 'nvarchar(50)') [OperationNumber],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountMainBranchDeposites') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountMainBranchDeposites] ([CommitteeBankAccountMainBranchDepositID],[CommitteeBankAccountID],[DepositDate],[DepositAmount],[OperationNumber])
	SELECT t.[CommitteeBankAccountMainBranchDepositID],t.[CommitteeBankAccountID],t.[DepositDate],t.[DepositAmount],t.[OperationNumber]
	FROM  @CommitteeBankAccountMainBranchDeposites_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountMainBranchDeposites_Tracking s
        WHERE t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountMainBranchDeposites]
	SET [CommitteeBankAccountMainBranchDeposites].[CommitteeBankAccountID] = t.[CommitteeBankAccountID],[CommitteeBankAccountMainBranchDeposites].[DepositDate] = t.[DepositDate],[CommitteeBankAccountMainBranchDeposites].[DepositAmount] = t.[DepositAmount],[CommitteeBankAccountMainBranchDeposites].[OperationNumber] = t.[OperationNumber] 
	FROM [dbo].[CommitteeBankAccountMainBranchDeposites] s JOIN @CommitteeBankAccountMainBranchDeposites_Temp t 
    ON t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountMainBranchDeposites_Tracking r
				  WHERE t.CommitteeBankAccountMainBranchDepositID = r.CommitteeBankAccountMainBranchDepositID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountMainBranchDeposites]
    FROM @CommitteeBankAccountMainBranchDeposites_Temp t JOIN [dbo].[CommitteeBankAccountMainBranchDeposites] s
    ON t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteeBankAccountMainBranchDepositAttachments_Tracking_Insert
ON [dbo].[CommitteeBankAccountMainBranchDepositAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking ([CommitteeBankAccountMainBranchDepositAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeBankAccountMainBranchDepositAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteeBankAccountMainBranchDepositAttachments_Tracking_Update
ON [dbo].[CommitteeBankAccountMainBranchDepositAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking s JOIN INSERTED i
    ON s.CommitteeBankAccountMainBranchDepositAttachmentID = i.CommitteeBankAccountMainBranchDepositAttachmentID
END

GO

ALTER TRIGGER CommitteeBankAccountMainBranchDepositAttachments_Tracking_Delete
ON [dbo].[CommitteeBankAccountMainBranchDepositAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking s JOIN DELETED i
    ON s.CommitteeBankAccountMainBranchDepositAttachmentID = i.CommitteeBankAccountMainBranchDepositAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDepositAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeBankAccountMainBranchDepositAttachmentID],
		g.[CommitteeBankAccountMainBranchDepositID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteeBankAccountMainBranchDepositAttachments] g RIGHT JOIN Sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking t  
    ON t.CommitteeBankAccountMainBranchDepositAttachmentID = g.CommitteeBankAccountMainBranchDepositAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDepositAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountMainBranchDepositAttachments_Temp TABLE 
		(
			CommitteeBankAccountMainBranchDepositAttachmentID UniqueIdentifier,
			[CommitteeBankAccountMainBranchDepositID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountMainBranchDepositAttachments_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountMainBranchDepositAttachmentID[1]','uniqueidentifier') CommitteeBankAccountMainBranchDepositAttachmentID,
                Tbl.Col.value('CommitteeBankAccountMainBranchDepositID[1]', 'uniqueidentifier') [CommitteeBankAccountMainBranchDepositID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountMainBranchDepositAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountMainBranchDepositAttachments] ([CommitteeBankAccountMainBranchDepositAttachmentID],[CommitteeBankAccountMainBranchDepositID],[Attachment])
	SELECT t.[CommitteeBankAccountMainBranchDepositAttachmentID],t.[CommitteeBankAccountMainBranchDepositID],t.[Attachment]
	FROM  @CommitteeBankAccountMainBranchDepositAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking s
        WHERE t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountMainBranchDepositAttachments]
	SET [CommitteeBankAccountMainBranchDepositAttachments].[CommitteeBankAccountMainBranchDepositID] = t.[CommitteeBankAccountMainBranchDepositID],[CommitteeBankAccountMainBranchDepositAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[CommitteeBankAccountMainBranchDepositAttachments] s JOIN @CommitteeBankAccountMainBranchDepositAttachments_Temp t 
    ON t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking r
				  WHERE t.CommitteeBankAccountMainBranchDepositAttachmentID = r.CommitteeBankAccountMainBranchDepositAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountMainBranchDepositAttachments]
    FROM @CommitteeBankAccountMainBranchDepositAttachments_Temp t JOIN [dbo].[CommitteeBankAccountMainBranchDepositAttachments] s
    ON t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommitteesURLs_Tracking_Insert
ON [dbo].[CommitteesURLs] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommitteesURLs_Tracking ([CommitteeURLID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteeURLID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommitteesURLs_Tracking_Update
ON [dbo].[CommitteesURLs] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommitteesURLs_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommitteesURLs_Tracking s JOIN INSERTED i
    ON s.CommitteeURLID = i.CommitteeURLID
END

GO

ALTER TRIGGER CommitteesURLs_Tracking_Delete
ON [dbo].[CommitteesURLs] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommitteesURLs_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommitteesURLs_Tracking s JOIN DELETED i
    ON s.CommitteeURLID = i.CommitteeURLID
END

GO

ALTER PROCEDURE Sync.SP_CommitteesURLs_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteeURLID],
		g.[CommitteeName],g.[CommitteeURL],g.[CommitteeID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommitteesURLs] g RIGHT JOIN Sync.CommitteesURLs_Tracking t  
    ON t.CommitteeURLID = g.CommitteeURLID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_CommitteesURLs_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteesURLs_Temp TABLE 
		(
			CommitteeURLID UniqueIdentifier,
			[CommitteeName] nvarchar(50),
				[CommitteeURL] nvarchar(250),
				[CommitteeID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteesURLs_Temp 
	   SELECT	Tbl.Col.value('CommitteeURLID[1]','uniqueidentifier') CommitteeURLID,
                Tbl.Col.value('CommitteeName[1]', 'nvarchar(50)') [CommitteeName],
Tbl.Col.value('CommitteeURL[1]', 'nvarchar(250)') [CommitteeURL],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteesURLs') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteesURLs] ([CommitteeURLID],[CommitteeName],[CommitteeURL],[CommitteeID])
	SELECT t.[CommitteeURLID],t.[CommitteeName],t.[CommitteeURL],t.[CommitteeID]
	FROM  @CommitteesURLs_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteesURLs_Tracking s
        WHERE t.CommitteeURLID = s.CommitteeURLID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteesURLs]
	SET [CommitteesURLs].[CommitteeName] = t.[CommitteeName],[CommitteesURLs].[CommitteeURL] = t.[CommitteeURL],[CommitteesURLs].[CommitteeID] = t.[CommitteeID] 
	FROM [dbo].[CommitteesURLs] s JOIN @CommitteesURLs_Temp t 
    ON t.CommitteeURLID = s.CommitteeURLID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteesURLs_Tracking r
				  WHERE t.CommitteeURLID = r.CommitteeURLID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteesURLs]
    FROM @CommitteesURLs_Temp t JOIN [dbo].[CommitteesURLs] s
    ON t.CommitteeURLID = s.CommitteeURLID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER CommittessVillages_Tracking_Insert
ON [dbo].[CommittessVillages] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.CommittessVillages_Tracking ([CommitteID],[VillageID] ,IsDeleted,LastUpdatedDate)
	SELECT [CommitteID],[VillageID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER CommittessVillages_Tracking_Update
ON [dbo].[CommittessVillages] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.CommittessVillages_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.CommittessVillages_Tracking s JOIN INSERTED i
    ON s.CommitteID = i.CommitteID AND s.VillageID = i.VillageID
END

GO

ALTER TRIGGER CommittessVillages_Tracking_Delete
ON [dbo].[CommittessVillages] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.CommittessVillages_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.CommittessVillages_Tracking s JOIN DELETED i
    ON s.CommitteID = i.CommitteID AND s.VillageID = i.VillageID
END

GO

ALTER PROCEDURE Sync.SP_CommittessVillages_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[CommitteID],t.[VillageID],
		
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[CommittessVillages] g RIGHT JOIN Sync.CommittessVillages_Tracking t  
    ON t.CommitteID = g.CommitteID AND t.VillageID = g.VillageID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_CommittessVillages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommittessVillages_Temp TABLE 
		(
			CommitteID UniqueIdentifier,
	VillageID UniqueIdentifier,
			
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommittessVillages_Temp 
	   SELECT	Tbl.Col.value('CommitteID[1]','uniqueidentifier') CommitteID,
Tbl.Col.value('VillageID[1]','uniqueidentifier') VillageID,
                
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommittessVillages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommittessVillages] ([CommitteID],[VillageID])
	SELECT t.[CommitteID],t.[VillageID]
	FROM  @CommittessVillages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommittessVillages_Tracking s
        WHERE t.CommitteID = s.CommitteID AND t.VillageID = s.VillageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommittessVillages]
    FROM @CommittessVillages_Temp t JOIN [dbo].[CommittessVillages] s
    ON t.CommitteID = s.CommitteID AND t.VillageID = s.VillageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanCancellingDebts_Tracking_Insert
ON [dbo].[LoanCancellingDebts] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanCancellingDebts_Tracking ([LoanCancellingDebtID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanCancellingDebtID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanCancellingDebts_Tracking_Update
ON [dbo].[LoanCancellingDebts] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanCancellingDebts_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanCancellingDebts_Tracking s JOIN INSERTED i
    ON s.LoanCancellingDebtID = i.LoanCancellingDebtID
END

GO

ALTER TRIGGER LoanCancellingDebts_Tracking_Delete
ON [dbo].[LoanCancellingDebts] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanCancellingDebts_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanCancellingDebts_Tracking s JOIN DELETED i
    ON s.LoanCancellingDebtID = i.LoanCancellingDebtID
END

GO

ALTER PROCEDURE Sync.SP_LoanCancellingDebts_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanCancellingDebtID],
		g.[LoanID],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanCancellingDebts] g RIGHT JOIN Sync.LoanCancellingDebts_Tracking t  
    ON t.LoanCancellingDebtID = g.LoanCancellingDebtID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanCancellingDebts_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanCancellingDebts_Temp TABLE 
		(
			LoanCancellingDebtID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanCancellingDebts_Temp 
	   SELECT	Tbl.Col.value('LoanCancellingDebtID[1]','uniqueidentifier') LoanCancellingDebtID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanCancellingDebts') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanCancellingDebts] ([LoanCancellingDebtID],[LoanID],[Notes])
	SELECT t.[LoanCancellingDebtID],t.[LoanID],t.[Notes]
	FROM  @LoanCancellingDebts_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanCancellingDebts_Tracking s
        WHERE t.LoanCancellingDebtID = s.LoanCancellingDebtID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanCancellingDebts]
	SET [LoanCancellingDebts].[LoanID] = t.[LoanID],[LoanCancellingDebts].[Notes] = t.[Notes] 
	FROM [dbo].[LoanCancellingDebts] s JOIN @LoanCancellingDebts_Temp t 
    ON t.LoanCancellingDebtID = s.LoanCancellingDebtID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanCancellingDebts_Tracking r
				  WHERE t.LoanCancellingDebtID = r.LoanCancellingDebtID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanCancellingDebts]
    FROM @LoanCancellingDebts_Temp t JOIN [dbo].[LoanCancellingDebts] s
    ON t.LoanCancellingDebtID = s.LoanCancellingDebtID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanCancellingDebtAttachments_Tracking_Insert
ON [dbo].[LoanCancellingDebtAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanCancellingDebtAttachments_Tracking ([LoanCancellingDebtAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanCancellingDebtAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanCancellingDebtAttachments_Tracking_Update
ON [dbo].[LoanCancellingDebtAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanCancellingDebtAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanCancellingDebtAttachments_Tracking s JOIN INSERTED i
    ON s.LoanCancellingDebtAttachmentID = i.LoanCancellingDebtAttachmentID
END

GO

ALTER TRIGGER LoanCancellingDebtAttachments_Tracking_Delete
ON [dbo].[LoanCancellingDebtAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanCancellingDebtAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanCancellingDebtAttachments_Tracking s JOIN DELETED i
    ON s.LoanCancellingDebtAttachmentID = i.LoanCancellingDebtAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanCancellingDebtAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanCancellingDebtAttachmentID],
		g.[LoanCancellingDebtID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanCancellingDebtAttachments] g RIGHT JOIN Sync.LoanCancellingDebtAttachments_Tracking t  
    ON t.LoanCancellingDebtAttachmentID = g.LoanCancellingDebtAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanCancellingDebtAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanCancellingDebtAttachments_Temp TABLE 
		(
			LoanCancellingDebtAttachmentID UniqueIdentifier,
			[LoanCancellingDebtID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanCancellingDebtAttachments_Temp 
	   SELECT	Tbl.Col.value('LoanCancellingDebtAttachmentID[1]','uniqueidentifier') LoanCancellingDebtAttachmentID,
                Tbl.Col.value('LoanCancellingDebtID[1]', 'uniqueidentifier') [LoanCancellingDebtID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanCancellingDebtAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanCancellingDebtAttachments] ([LoanCancellingDebtAttachmentID],[LoanCancellingDebtID],[Attachment])
	SELECT t.[LoanCancellingDebtAttachmentID],t.[LoanCancellingDebtID],t.[Attachment]
	FROM  @LoanCancellingDebtAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanCancellingDebtAttachments_Tracking s
        WHERE t.LoanCancellingDebtAttachmentID = s.LoanCancellingDebtAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanCancellingDebtAttachments]
	SET [LoanCancellingDebtAttachments].[LoanCancellingDebtID] = t.[LoanCancellingDebtID],[LoanCancellingDebtAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[LoanCancellingDebtAttachments] s JOIN @LoanCancellingDebtAttachments_Temp t 
    ON t.LoanCancellingDebtAttachmentID = s.LoanCancellingDebtAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanCancellingDebtAttachments_Tracking r
				  WHERE t.LoanCancellingDebtAttachmentID = r.LoanCancellingDebtAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanCancellingDebtAttachments]
    FROM @LoanCancellingDebtAttachments_Temp t JOIN [dbo].[LoanCancellingDebtAttachments] s
    ON t.LoanCancellingDebtAttachmentID = s.LoanCancellingDebtAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanCases_Tracking_Insert
ON [dbo].[LoanCases] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanCases_Tracking ([LoanCaseID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanCaseID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanCases_Tracking_Update
ON [dbo].[LoanCases] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanCases_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanCases_Tracking s JOIN INSERTED i
    ON s.LoanCaseID = i.LoanCaseID
END

GO

ALTER TRIGGER LoanCases_Tracking_Delete
ON [dbo].[LoanCases] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanCases_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanCases_Tracking s JOIN DELETED i
    ON s.LoanCaseID = i.LoanCaseID
END

GO

ALTER PROCEDURE Sync.SP_LoanCases_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanCaseID],
		g.[LoanID],g.[CaseNumber],g.[CaseDate],g.[Notes],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanCases] g RIGHT JOIN Sync.LoanCases_Tracking t  
    ON t.LoanCaseID = g.LoanCaseID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanCases_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanCases_Temp TABLE 
		(
			LoanCaseID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[CaseNumber] bigint,
				[CaseDate] datetime,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanCases_Temp 
	   SELECT	Tbl.Col.value('LoanCaseID[1]','uniqueidentifier') LoanCaseID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('CaseNumber[1]', 'bigint') [CaseNumber],
Tbl.Col.value('CaseDate[1]', 'datetime') [CaseDate],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanCases') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanCases] ([LoanCaseID],[LoanID],[CaseNumber],[CaseDate],[Notes])
	SELECT t.[LoanCaseID],t.[LoanID],t.[CaseNumber],t.[CaseDate],t.[Notes]
	FROM  @LoanCases_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanCases_Tracking s
        WHERE t.LoanCaseID = s.LoanCaseID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanCases]
	SET [LoanCases].[LoanID] = t.[LoanID],[LoanCases].[CaseNumber] = t.[CaseNumber],[LoanCases].[CaseDate] = t.[CaseDate],[LoanCases].[Notes] = t.[Notes] 
	FROM [dbo].[LoanCases] s JOIN @LoanCases_Temp t 
    ON t.LoanCaseID = s.LoanCaseID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanCases_Tracking r
				  WHERE t.LoanCaseID = r.LoanCaseID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanCases]
    FROM @LoanCases_Temp t JOIN [dbo].[LoanCases] s
    ON t.LoanCaseID = s.LoanCaseID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanExecution_Tracking_Insert
ON [dbo].[LoanExecution] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanExecution_Tracking ([LoanExecutionID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanExecutionID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanExecution_Tracking_Update
ON [dbo].[LoanExecution] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanExecution_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanExecution_Tracking s JOIN INSERTED i
    ON s.LoanExecutionID = i.LoanExecutionID
END

GO

ALTER TRIGGER LoanExecution_Tracking_Delete
ON [dbo].[LoanExecution] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanExecution_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanExecution_Tracking s JOIN DELETED i
    ON s.LoanExecutionID = i.LoanExecutionID
END

GO

ALTER PROCEDURE Sync.SP_LoanExecution_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanExecutionID],
		g.[LoanID],g.[GuarantorName],g.[GuarantorSSN],g.[InterestPercentage],g.[LoanPaymentMethodID],g.[LoanAmount],g.[LoanPeriod],g.[TotalLoanAmount],g.[ExecutionDate],g.[LoanExecutionStatus],g.[CheckNumber],g.[CheckDate],g.[GuarantorAddress],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanExecution] g RIGHT JOIN Sync.LoanExecution_Tracking t  
    ON t.LoanExecutionID = g.LoanExecutionID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanExecution_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanExecution_Temp TABLE 
		(
			LoanExecutionID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[GuarantorName] nvarchar(50),
				[GuarantorSSN] nvarchar(50),
				[InterestPercentage] decimal,
				[LoanPaymentMethodID] uniqueidentifier,
				[LoanAmount] float,
				[LoanPeriod] int,
				[TotalLoanAmount] float,
				[ExecutionDate] datetime,
				[LoanExecutionStatus] bit,
				[CheckNumber] nvarchar(50),
				[CheckDate] datetime,
				[GuarantorAddress] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanExecution_Temp 
	   SELECT	Tbl.Col.value('LoanExecutionID[1]','uniqueidentifier') LoanExecutionID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('GuarantorName[1]', 'nvarchar(50)') [GuarantorName],
Tbl.Col.value('GuarantorSSN[1]', 'nvarchar(50)') [GuarantorSSN],
Tbl.Col.value('InterestPercentage[1]', 'decimal') [InterestPercentage],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPeriod[1]', 'int') [LoanPeriod],
Tbl.Col.value('TotalLoanAmount[1]', 'float') [TotalLoanAmount],
Tbl.Col.value('ExecutionDate[1]', 'datetime') [ExecutionDate],
Tbl.Col.value('LoanExecutionStatus[1]', 'bit') [LoanExecutionStatus],
Tbl.Col.value('CheckNumber[1]', 'nvarchar(50)') [CheckNumber],
Tbl.Col.value('CheckDate[1]', 'datetime') [CheckDate],
Tbl.Col.value('GuarantorAddress[1]', 'nvarchar(50)') [GuarantorAddress],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanExecution') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanExecution] ([LoanExecutionID],[LoanID],[GuarantorName],[GuarantorSSN],[InterestPercentage],[LoanPaymentMethodID],[LoanAmount],[LoanPeriod],[TotalLoanAmount],[ExecutionDate],[LoanExecutionStatus],[CheckNumber],[CheckDate],[GuarantorAddress])
	SELECT t.[LoanExecutionID],t.[LoanID],t.[GuarantorName],t.[GuarantorSSN],t.[InterestPercentage],t.[LoanPaymentMethodID],t.[LoanAmount],t.[LoanPeriod],t.[TotalLoanAmount],t.[ExecutionDate],t.[LoanExecutionStatus],t.[CheckNumber],t.[CheckDate],t.[GuarantorAddress]
	FROM  @LoanExecution_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanExecution_Tracking s
        WHERE t.LoanExecutionID = s.LoanExecutionID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanExecution]
	SET [LoanExecution].[LoanID] = t.[LoanID],[LoanExecution].[GuarantorName] = t.[GuarantorName],[LoanExecution].[GuarantorSSN] = t.[GuarantorSSN],[LoanExecution].[InterestPercentage] = t.[InterestPercentage],[LoanExecution].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[LoanExecution].[LoanAmount] = t.[LoanAmount],[LoanExecution].[LoanPeriod] = t.[LoanPeriod],[LoanExecution].[TotalLoanAmount] = t.[TotalLoanAmount],[LoanExecution].[ExecutionDate] = t.[ExecutionDate],[LoanExecution].[LoanExecutionStatus] = t.[LoanExecutionStatus],[LoanExecution].[CheckNumber] = t.[CheckNumber],[LoanExecution].[CheckDate] = t.[CheckDate],[LoanExecution].[GuarantorAddress] = t.[GuarantorAddress] 
	FROM [dbo].[LoanExecution] s JOIN @LoanExecution_Temp t 
    ON t.LoanExecutionID = s.LoanExecutionID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanExecution_Tracking r
				  WHERE t.LoanExecutionID = r.LoanExecutionID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanExecution]
    FROM @LoanExecution_Temp t JOIN [dbo].[LoanExecution] s
    ON t.LoanExecutionID = s.LoanExecutionID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanExecutionAttachments_Tracking_Insert
ON [dbo].[LoanExecutionAttachments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanExecutionAttachments_Tracking ([LoanExecutionAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanExecutionAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanExecutionAttachments_Tracking_Update
ON [dbo].[LoanExecutionAttachments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanExecutionAttachments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanExecutionAttachments_Tracking s JOIN INSERTED i
    ON s.LoanExecutionAttachmentID = i.LoanExecutionAttachmentID
END

GO

ALTER TRIGGER LoanExecutionAttachments_Tracking_Delete
ON [dbo].[LoanExecutionAttachments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanExecutionAttachments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanExecutionAttachments_Tracking s JOIN DELETED i
    ON s.LoanExecutionAttachmentID = i.LoanExecutionAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanExecutionAttachments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanExecutionAttachmentID],
		g.[LoanExecutionID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanExecutionAttachments] g RIGHT JOIN Sync.LoanExecutionAttachments_Tracking t  
    ON t.LoanExecutionAttachmentID = g.LoanExecutionAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanExecutionAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanExecutionAttachments_Temp TABLE 
		(
			LoanExecutionAttachmentID UniqueIdentifier,
			[LoanExecutionID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanExecutionAttachments_Temp 
	   SELECT	Tbl.Col.value('LoanExecutionAttachmentID[1]','uniqueidentifier') LoanExecutionAttachmentID,
                Tbl.Col.value('LoanExecutionID[1]', 'uniqueidentifier') [LoanExecutionID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanExecutionAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanExecutionAttachments] ([LoanExecutionAttachmentID],[LoanExecutionID],[Attachment])
	SELECT t.[LoanExecutionAttachmentID],t.[LoanExecutionID],t.[Attachment]
	FROM  @LoanExecutionAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanExecutionAttachments_Tracking s
        WHERE t.LoanExecutionAttachmentID = s.LoanExecutionAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanExecutionAttachments]
	SET [LoanExecutionAttachments].[LoanExecutionID] = t.[LoanExecutionID],[LoanExecutionAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[LoanExecutionAttachments] s JOIN @LoanExecutionAttachments_Temp t 
    ON t.LoanExecutionAttachmentID = s.LoanExecutionAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanExecutionAttachments_Tracking r
				  WHERE t.LoanExecutionAttachmentID = r.LoanExecutionAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanExecutionAttachments]
    FROM @LoanExecutionAttachments_Temp t JOIN [dbo].[LoanExecutionAttachments] s
    ON t.LoanExecutionAttachmentID = s.LoanExecutionAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanExecutionInstallments_Tracking_Insert
ON [dbo].[LoanExecutionInstallments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanExecutionInstallments_Tracking ([LoanExecutionInstallmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanExecutionInstallmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanExecutionInstallments_Tracking_Update
ON [dbo].[LoanExecutionInstallments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanExecutionInstallments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanExecutionInstallments_Tracking s JOIN INSERTED i
    ON s.LoanExecutionInstallmentID = i.LoanExecutionInstallmentID
END

GO

ALTER TRIGGER LoanExecutionInstallments_Tracking_Delete
ON [dbo].[LoanExecutionInstallments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanExecutionInstallments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanExecutionInstallments_Tracking s JOIN DELETED i
    ON s.LoanExecutionInstallmentID = i.LoanExecutionInstallmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanExecutionInstallments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanExecutionInstallmentID],
		g.[LoanExecutionID],g.[InstallmentDate],g.[OriginalInstallmentAmount],g.[InterestInstallmentAmount],g.[TotalInstallmentAmount],g.[PenaltyDays],g.[PenaltyAmount],g.[IsPaid],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanExecutionInstallments] g RIGHT JOIN Sync.LoanExecutionInstallments_Tracking t  
    ON t.LoanExecutionInstallmentID = g.LoanExecutionInstallmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanExecutionInstallments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanExecutionInstallments_Temp TABLE 
		(
			LoanExecutionInstallmentID UniqueIdentifier,
			[LoanExecutionID] uniqueidentifier,
				[InstallmentDate] datetime,
				[OriginalInstallmentAmount] float,
				[InterestInstallmentAmount] float,
				[TotalInstallmentAmount] float,
				[PenaltyDays] int,
				[PenaltyAmount] float,
				[IsPaid] bit,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanExecutionInstallments_Temp 
	   SELECT	Tbl.Col.value('LoanExecutionInstallmentID[1]','uniqueidentifier') LoanExecutionInstallmentID,
                Tbl.Col.value('LoanExecutionID[1]', 'uniqueidentifier') [LoanExecutionID],
Tbl.Col.value('InstallmentDate[1]', 'datetime') [InstallmentDate],
Tbl.Col.value('OriginalInstallmentAmount[1]', 'float') [OriginalInstallmentAmount],
Tbl.Col.value('InterestInstallmentAmount[1]', 'float') [InterestInstallmentAmount],
Tbl.Col.value('TotalInstallmentAmount[1]', 'float') [TotalInstallmentAmount],
Tbl.Col.value('PenaltyDays[1]', 'int') [PenaltyDays],
Tbl.Col.value('PenaltyAmount[1]', 'float') [PenaltyAmount],
Tbl.Col.value('IsPaid[1]', 'bit') [IsPaid],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanExecutionInstallments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanExecutionInstallments] ([LoanExecutionInstallmentID],[LoanExecutionID],[InstallmentDate],[OriginalInstallmentAmount],[InterestInstallmentAmount],[TotalInstallmentAmount],[PenaltyDays],[PenaltyAmount],[IsPaid])
	SELECT t.[LoanExecutionInstallmentID],t.[LoanExecutionID],t.[InstallmentDate],t.[OriginalInstallmentAmount],t.[InterestInstallmentAmount],t.[TotalInstallmentAmount],t.[PenaltyDays],t.[PenaltyAmount],t.[IsPaid]
	FROM  @LoanExecutionInstallments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanExecutionInstallments_Tracking s
        WHERE t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanExecutionInstallments]
	SET [LoanExecutionInstallments].[LoanExecutionID] = t.[LoanExecutionID],[LoanExecutionInstallments].[InstallmentDate] = t.[InstallmentDate],[LoanExecutionInstallments].[OriginalInstallmentAmount] = t.[OriginalInstallmentAmount],[LoanExecutionInstallments].[InterestInstallmentAmount] = t.[InterestInstallmentAmount],[LoanExecutionInstallments].[TotalInstallmentAmount] = t.[TotalInstallmentAmount],[LoanExecutionInstallments].[PenaltyDays] = t.[PenaltyDays],[LoanExecutionInstallments].[PenaltyAmount] = t.[PenaltyAmount],[LoanExecutionInstallments].[IsPaid] = t.[IsPaid] 
	FROM [dbo].[LoanExecutionInstallments] s JOIN @LoanExecutionInstallments_Temp t 
    ON t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanExecutionInstallments_Tracking r
				  WHERE t.LoanExecutionInstallmentID = r.LoanExecutionInstallmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanExecutionInstallments]
    FROM @LoanExecutionInstallments_Temp t JOIN [dbo].[LoanExecutionInstallments] s
    ON t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanInstallmentPayments_Tracking_Insert
ON [dbo].[LoanInstallmentPayments] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanInstallmentPayments_Tracking ([LoanInstallmentPaymentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanInstallmentPaymentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanInstallmentPayments_Tracking_Update
ON [dbo].[LoanInstallmentPayments] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanInstallmentPayments_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanInstallmentPayments_Tracking s JOIN INSERTED i
    ON s.LoanInstallmentPaymentID = i.LoanInstallmentPaymentID
END

GO

ALTER TRIGGER LoanInstallmentPayments_Tracking_Delete
ON [dbo].[LoanInstallmentPayments] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanInstallmentPayments_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanInstallmentPayments_Tracking s JOIN DELETED i
    ON s.LoanInstallmentPaymentID = i.LoanInstallmentPaymentID
END

GO

ALTER PROCEDURE Sync.SP_LoanInstallmentPayments_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanInstallmentPaymentID],
		g.[LoanExecutionInstallmentID],g.[PaymentAmount],g.[PaymentDate],g.[ReceiptID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanInstallmentPayments] g RIGHT JOIN Sync.LoanInstallmentPayments_Tracking t  
    ON t.LoanInstallmentPaymentID = g.LoanInstallmentPaymentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanInstallmentPayments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanInstallmentPayments_Temp TABLE 
		(
			LoanInstallmentPaymentID UniqueIdentifier,
			[LoanExecutionInstallmentID] uniqueidentifier,
				[PaymentAmount] float,
				[PaymentDate] datetime,
				[ReceiptID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanInstallmentPayments_Temp 
	   SELECT	Tbl.Col.value('LoanInstallmentPaymentID[1]','uniqueidentifier') LoanInstallmentPaymentID,
                Tbl.Col.value('LoanExecutionInstallmentID[1]', 'uniqueidentifier') [LoanExecutionInstallmentID],
Tbl.Col.value('PaymentAmount[1]', 'float') [PaymentAmount],
Tbl.Col.value('PaymentDate[1]', 'datetime') [PaymentDate],
Tbl.Col.value('ReceiptID[1]', 'uniqueidentifier') [ReceiptID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanInstallmentPayments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanInstallmentPayments] ([LoanInstallmentPaymentID],[LoanExecutionInstallmentID],[PaymentAmount],[PaymentDate],[ReceiptID])
	SELECT t.[LoanInstallmentPaymentID],t.[LoanExecutionInstallmentID],t.[PaymentAmount],t.[PaymentDate],t.[ReceiptID]
	FROM  @LoanInstallmentPayments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanInstallmentPayments_Tracking s
        WHERE t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanInstallmentPayments]
	SET [LoanInstallmentPayments].[LoanExecutionInstallmentID] = t.[LoanExecutionInstallmentID],[LoanInstallmentPayments].[PaymentAmount] = t.[PaymentAmount],[LoanInstallmentPayments].[PaymentDate] = t.[PaymentDate],[LoanInstallmentPayments].[ReceiptID] = t.[ReceiptID] 
	FROM [dbo].[LoanInstallmentPayments] s JOIN @LoanInstallmentPayments_Temp t 
    ON t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanInstallmentPayments_Tracking r
				  WHERE t.LoanInstallmentPaymentID = r.LoanInstallmentPaymentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanInstallmentPayments]
    FROM @LoanInstallmentPayments_Temp t JOIN [dbo].[LoanInstallmentPayments] s
    ON t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanIntstallmentWarnings_Tracking_Insert
ON [dbo].[LoanIntstallmentWarnings] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanIntstallmentWarnings_Tracking ([LoanIntstallmentWarningID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanIntstallmentWarningID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanIntstallmentWarnings_Tracking_Update
ON [dbo].[LoanIntstallmentWarnings] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanIntstallmentWarnings_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanIntstallmentWarnings_Tracking s JOIN INSERTED i
    ON s.LoanIntstallmentWarningID = i.LoanIntstallmentWarningID
END

GO

ALTER TRIGGER LoanIntstallmentWarnings_Tracking_Delete
ON [dbo].[LoanIntstallmentWarnings] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanIntstallmentWarnings_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanIntstallmentWarnings_Tracking s JOIN DELETED i
    ON s.LoanIntstallmentWarningID = i.LoanIntstallmentWarningID
END

GO

ALTER PROCEDURE Sync.SP_LoanIntstallmentWarnings_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanIntstallmentWarningID],
		g.[LoanExecutionInstallmentID],g.[WarningDate],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanIntstallmentWarnings] g RIGHT JOIN Sync.LoanIntstallmentWarnings_Tracking t  
    ON t.LoanIntstallmentWarningID = g.LoanIntstallmentWarningID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanIntstallmentWarnings_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanIntstallmentWarnings_Temp TABLE 
		(
			LoanIntstallmentWarningID UniqueIdentifier,
			[LoanExecutionInstallmentID] uniqueidentifier,
				[WarningDate] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanIntstallmentWarnings_Temp 
	   SELECT	Tbl.Col.value('LoanIntstallmentWarningID[1]','uniqueidentifier') LoanIntstallmentWarningID,
                Tbl.Col.value('LoanExecutionInstallmentID[1]', 'uniqueidentifier') [LoanExecutionInstallmentID],
Tbl.Col.value('WarningDate[1]', 'datetime') [WarningDate],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanIntstallmentWarnings') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanIntstallmentWarnings] ([LoanIntstallmentWarningID],[LoanExecutionInstallmentID],[WarningDate])
	SELECT t.[LoanIntstallmentWarningID],t.[LoanExecutionInstallmentID],t.[WarningDate]
	FROM  @LoanIntstallmentWarnings_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanIntstallmentWarnings_Tracking s
        WHERE t.LoanIntstallmentWarningID = s.LoanIntstallmentWarningID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanIntstallmentWarnings]
	SET [LoanIntstallmentWarnings].[LoanExecutionInstallmentID] = t.[LoanExecutionInstallmentID],[LoanIntstallmentWarnings].[WarningDate] = t.[WarningDate] 
	FROM [dbo].[LoanIntstallmentWarnings] s JOIN @LoanIntstallmentWarnings_Temp t 
    ON t.LoanIntstallmentWarningID = s.LoanIntstallmentWarningID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanIntstallmentWarnings_Tracking r
				  WHERE t.LoanIntstallmentWarningID = r.LoanIntstallmentWarningID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanIntstallmentWarnings]
    FROM @LoanIntstallmentWarnings_Temp t JOIN [dbo].[LoanIntstallmentWarnings] s
    ON t.LoanIntstallmentWarningID = s.LoanIntstallmentWarningID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER LoanInstallmentWarningAttachment_Tracking_Insert
ON [dbo].[LoanInstallmentWarningAttachment] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.LoanInstallmentWarningAttachment_Tracking ([LoanInstallmentWarningAttachmentID] ,IsDeleted,LastUpdatedDate)
	SELECT [LoanInstallmentWarningAttachmentID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER LoanInstallmentWarningAttachment_Tracking_Update
ON [dbo].[LoanInstallmentWarningAttachment] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.LoanInstallmentWarningAttachment_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.LoanInstallmentWarningAttachment_Tracking s JOIN INSERTED i
    ON s.LoanInstallmentWarningAttachmentID = i.LoanInstallmentWarningAttachmentID
END

GO

ALTER TRIGGER LoanInstallmentWarningAttachment_Tracking_Delete
ON [dbo].[LoanInstallmentWarningAttachment] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.LoanInstallmentWarningAttachment_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.LoanInstallmentWarningAttachment_Tracking s JOIN DELETED i
    ON s.LoanInstallmentWarningAttachmentID = i.LoanInstallmentWarningAttachmentID
END

GO

ALTER PROCEDURE Sync.SP_LoanInstallmentWarningAttachment_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[LoanInstallmentWarningAttachmentID],
		g.[LoanIntallmentWarningID],g.[Attachment],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[LoanInstallmentWarningAttachment] g RIGHT JOIN Sync.LoanInstallmentWarningAttachment_Tracking t  
    ON t.LoanInstallmentWarningAttachmentID = g.LoanInstallmentWarningAttachmentID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_LoanInstallmentWarningAttachment_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanInstallmentWarningAttachment_Temp TABLE 
		(
			LoanInstallmentWarningAttachmentID UniqueIdentifier,
			[LoanIntallmentWarningID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanInstallmentWarningAttachment_Temp 
	   SELECT	Tbl.Col.value('LoanInstallmentWarningAttachmentID[1]','uniqueidentifier') LoanInstallmentWarningAttachmentID,
                Tbl.Col.value('LoanIntallmentWarningID[1]', 'uniqueidentifier') [LoanIntallmentWarningID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanInstallmentWarningAttachment') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanInstallmentWarningAttachment] ([LoanInstallmentWarningAttachmentID],[LoanIntallmentWarningID],[Attachment])
	SELECT t.[LoanInstallmentWarningAttachmentID],t.[LoanIntallmentWarningID],t.[Attachment]
	FROM  @LoanInstallmentWarningAttachment_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanInstallmentWarningAttachment_Tracking s
        WHERE t.LoanInstallmentWarningAttachmentID = s.LoanInstallmentWarningAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanInstallmentWarningAttachment]
	SET [LoanInstallmentWarningAttachment].[LoanIntallmentWarningID] = t.[LoanIntallmentWarningID],[LoanInstallmentWarningAttachment].[Attachment] = t.[Attachment] 
	FROM [dbo].[LoanInstallmentWarningAttachment] s JOIN @LoanInstallmentWarningAttachment_Temp t 
    ON t.LoanInstallmentWarningAttachmentID = s.LoanInstallmentWarningAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanInstallmentWarningAttachment_Tracking r
				  WHERE t.LoanInstallmentWarningAttachmentID = r.LoanInstallmentWarningAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanInstallmentWarningAttachment]
    FROM @LoanInstallmentWarningAttachment_Temp t JOIN [dbo].[LoanInstallmentWarningAttachment] s
    ON t.LoanInstallmentWarningAttachmentID = s.LoanInstallmentWarningAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER VacationYearDays_Tracking_Insert
ON [dbo].[VacationYearDays] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.VacationYearDays_Tracking ([VacationYearDayID] ,IsDeleted,LastUpdatedDate)
	SELECT [VacationYearDayID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER VacationYearDays_Tracking_Update
ON [dbo].[VacationYearDays] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.VacationYearDays_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.VacationYearDays_Tracking s JOIN INSERTED i
    ON s.VacationYearDayID = i.VacationYearDayID
END

GO

ALTER TRIGGER VacationYearDays_Tracking_Delete
ON [dbo].[VacationYearDays] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.VacationYearDays_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.VacationYearDays_Tracking s JOIN DELETED i
    ON s.VacationYearDayID = i.VacationYearDayID
END

GO

ALTER PROCEDURE Sync.SP_VacationYearDays_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[VacationYearDayID],
		g.[VacationYearID],g.[Day],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[VacationYearDays] g RIGHT JOIN Sync.VacationYearDays_Tracking t  
    ON t.VacationYearDayID = g.VacationYearDayID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_VacationYearDays_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @VacationYearDays_Temp TABLE 
		(
			VacationYearDayID UniqueIdentifier,
			[VacationYearID] uniqueidentifier,
				[Day] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @VacationYearDays_Temp 
	   SELECT	Tbl.Col.value('VacationYearDayID[1]','uniqueidentifier') VacationYearDayID,
                Tbl.Col.value('VacationYearID[1]', 'uniqueidentifier') [VacationYearID],
Tbl.Col.value('Day[1]', 'datetime') [Day],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/VacationYearDays') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[VacationYearDays] ([VacationYearDayID],[VacationYearID],[Day])
	SELECT t.[VacationYearDayID],t.[VacationYearID],t.[Day]
	FROM  @VacationYearDays_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.VacationYearDays_Tracking s
        WHERE t.VacationYearDayID = s.VacationYearDayID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[VacationYearDays]
	SET [VacationYearDays].[VacationYearID] = t.[VacationYearID],[VacationYearDays].[Day] = t.[Day] 
	FROM [dbo].[VacationYearDays] s JOIN @VacationYearDays_Temp t 
    ON t.VacationYearDayID = s.VacationYearDayID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.VacationYearDays_Tracking r
				  WHERE t.VacationYearDayID = r.VacationYearDayID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[VacationYearDays]
    FROM @VacationYearDays_Temp t JOIN [dbo].[VacationYearDays] s
    ON t.VacationYearDayID = s.VacationYearDayID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER VacationYears_Tracking_Insert
ON [dbo].[VacationYears] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.VacationYears_Tracking ([VacationYearID] ,IsDeleted,LastUpdatedDate)
	SELECT [VacationYearID],0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER VacationYears_Tracking_Update
ON [dbo].[VacationYears] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.VacationYears_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.VacationYears_Tracking s JOIN INSERTED i
    ON s.VacationYearID = i.VacationYearID
END

GO

ALTER TRIGGER VacationYears_Tracking_Delete
ON [dbo].[VacationYears] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.VacationYears_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.VacationYears_Tracking s JOIN DELETED i
    ON s.VacationYearID = i.VacationYearID
END

GO

ALTER PROCEDURE Sync.SP_VacationYears_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        t.[VacationYearID],
		g.[VacationYear],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[VacationYears] g RIGHT JOIN Sync.VacationYears_Tracking t  
    ON t.VacationYearID = g.VacationYearID 
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
    END 

GO

ALTER PROCEDURE Sync.SP_VacationYears_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @VacationYears_Temp TABLE 
		(
			VacationYearID UniqueIdentifier,
			[VacationYear] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @VacationYears_Temp 
	   SELECT	Tbl.Col.value('VacationYearID[1]','uniqueidentifier') VacationYearID,
                Tbl.Col.value('VacationYear[1]', 'nvarchar(50)') [VacationYear],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/VacationYears') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[VacationYears] ([VacationYearID],[VacationYear])
	SELECT t.[VacationYearID],t.[VacationYear]
	FROM  @VacationYears_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.VacationYears_Tracking s
        WHERE t.VacationYearID = s.VacationYearID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[VacationYears]
	SET [VacationYears].[VacationYear] = t.[VacationYear] 
	FROM [dbo].[VacationYears] s JOIN @VacationYears_Temp t 
    ON t.VacationYearID = s.VacationYearID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.VacationYears_Tracking r
				  WHERE t.VacationYearID = r.VacationYearID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[VacationYears]
    FROM @VacationYears_Temp t JOIN [dbo].[VacationYears] s
    ON t.VacationYearID = s.VacationYearID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
ALTER TRIGGER Settings_Tracking_Insert
ON [dbo].[Settings] 
AFTER INSERT
AS 
BEGIN
	INSERT INTO Sync.Settings_Tracking ( ,IsDeleted,LastUpdatedDate)
	SELECT ,0,NULL
	FROM INSERTED
END 
GO

ALTER TRIGGER Settings_Tracking_Update
ON [dbo].[Settings] 
AFTER Update
AS 
BEGIN
	UPDATE Sync.Settings_Tracking 
	SET LastUpdatedDate  = getdate()
    FROM Sync.Settings_Tracking s JOIN INSERTED i
    ON 
END

GO

ALTER TRIGGER Settings_Tracking_Delete
ON [dbo].[Settings] 
AFTER delete
AS 
BEGIN
	UPDATE Sync.Settings_Tracking 
	SET LastUpdatedDate  = getdate(),
		IsDeleted = 1
	FROM Sync.Settings_Tracking s JOIN DELETED i
    ON 
END

GO

ALTER PROCEDURE Sync.SP_Settings_SelectChanges
@LastRowVersion rowversion,
@FilterdColumn UNIQUEIDENTIFIER
AS 
BEGIN 
	SELECT 
        ,
		g.[Key],g.[Value],g.[CommitteeID],g.[ID],
		t.IsDeleted,
		t.LastUpdatedDate
	FROM [dbo].[Settings] g RIGHT JOIN Sync.Settings_Tracking t  
    ON  
	WHERE (@LastRowVersion IS NULL OR t.Version > @LastRowVersion)
        AND (@FilterdColumn IS NULL OR g.CommitteeID = @FilterdColumn)
    END 

GO

ALTER PROCEDURE Sync.SP_Settings_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Settings_Temp TABLE 
		(
			,
			[Key] nvarchar(50),
				[Value] nvarchar(50),
				[CommitteeID] uniqueidentifier,
				[ID] int,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Settings_Temp 
	   SELECT	,
                Tbl.Col.value('Key[1]', 'nvarchar(50)') [Key],
Tbl.Col.value('Value[1]', 'nvarchar(50)') [Value],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('ID[1]', 'int') [ID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Settings') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Settings] (,[Key],[Value],[CommitteeID],[ID])
	SELECT ,t.[Key],t.[Value],t.[CommitteeID],t.[ID]
	FROM  @Settings_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Settings_Tracking s
        WHERE 
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Settings]
	SET [Settings].[Key] = t.[Key],[Settings].[Value] = t.[Value],[Settings].[CommitteeID] = t.[CommitteeID],[Settings].[ID] = t.[ID] 
	FROM [dbo].[Settings] s JOIN @Settings_Temp t 
    ON 
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Settings_Tracking r
				  WHERE )
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Settings]
    FROM @Settings_Temp t JOIN [dbo].[Settings] s
    ON 
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
